1. Premessa
Sqlite ha 5 funzioni per il trattamento delle date e delle ore
1. date(time-value, modifier, modifier, ...)
2. time(time-value, modifier, modifier, ...)
3. datetime(time-value, modifier, modifier, ...)
4. julianday(time-value, modifier, modifier, ...)
5. strftime(format, time-value, modifier, modifier, ...)
Tutte le funzioni richiedono come argomento un valore temporale ( “time-value”) , seguito da 0 o più argomenti (“modifier”).
La funzione strftime() richiede, in più, come primo argomento una stringa di formato.
Le funzioni di data e ora usano un subset dei formati data e ora di IS0-8601
La funzione date() restituisce una data nel formato: YYYY-MM-DD
La funzione time() restituisce un’ora nel formato: HH:MM:SS
La funzione datetime() restituisce una data-ora nel formato: YYYY-MM-DD HH:MM:SS
La funzione julianday() restituisce il Julian day – numero di giorni dal mezzogiorno, in Greenwich, del 24 Novembre, 4714 B.C. (B.C. = Before Christ ed è equivalente a A.C. - Proleptic Gregorian calendar)
La funzione strftime() restituisce la data formattata secondo il formato indicato come primo argomento. La stringa di formato può utilizzare i seguenti specificatori di formato
%d giorno del mese: 00
%f secondi: SS.SSS
%H ore: 00-24
%j giorno dell’anno: 001-366
%J numero giorno giuliano
%m mese: 01-12
%M minuti: 00-59
%s secondi dal 1970-01-01
%S secondi: 00-59
%w giorno della settimana 0-6 con Domenica == 0
%W settimana dell’anno: 00-53
%Y anno: 0000-9999
%% %
Equivalenza con strftime()
Tutte le altre funzioni di data-ora possono essere riscritte utilizzando strftime()
date(...) strftime('%Y-%m-%d', ...)
time(...) strftime('%H:%M:%S', ...)
datetime(...) strftime('%Y-%m-%d %H:%M:%S', ...)
julianday(...) strftime('%J', ...)
2. Time-Value (parametro time-value)
Il valore del parametro time-value può essere rappresentato secondo uno dei seguenti formati; di solito è una stringa, ma nel caso del formato 12 può essere un intero o un numero floating point.
1. YYYY-MM-DD
2. YYYY-MM-DD HH:MM
3. YYYY-MM-DD HH:MM:SS
4. YYYY-MM-DD HH:MM:SS.SSS
5. YYYY-MM-DDTHH:MM
6. YYYY-MM-DDTHH:MM:SS
7. YYYY-MM-DDTHH:MM:SS.SSS
8. HH:MM
9. HH:MM:SS
10. HH:MM:SS.SSS
11. now
12. DDDDDDDDDD
Nei formati da 5 a 7, il carattere “T” serve da separatore tra data e ora secondo lo standard richiesto da ISO-8601 (<data>T<ora>).
I formati da da 8 a 10 sottindendono come data il valore 2000-01-01.
Nel formato 11 la stringa now è convertita nel valore corrente di data, ora UTC (Universal Coordinated Time).
Il formato 12 è il formato utilizzato per indicare un Julian day number espresso da un intero o da un valore floating point.
I formati da 2 a 10 possono essere opzionalmente seguiti dall’indicatore del fuso orario (timezone) nella forma "[+-]HH:MM" o semplicemente "Z"; da tener presente che le funzioni di data/ora indicano sempre la data/ora UTC o “zulu” time, quindi il suffisso “Z” è inutile.
Qualsiasi valore del suffisso "[+-]HH:MM" viene automaticamente sottratto(*) dalla data/ora indicata, ad es.
SELECT datetime('2022-01-20 13:00+01:00'); restituisce '2022-01-20 12:00’
SELECT datetime('2022-01-20 13:00-01:00'); restituisce '2022-01-20 14:00’
Tutte le date/ora seguenti sono equivalenti:
2013-10-07 08:23:19.120
2013-10-07T08:23:19.120Z
2013-10-07 04:23:19.120-04:00
2456572.84952685
Nei formati 4, 7, e 10, il valore dei secondi SS.SSS può avere una o più cifre decimali, ma solo 3 cifre sono significative ai fini del risultato. Così nel formato 12 sono indicate 10 cifre ma in effetti si possono utilizzare più o meno cifre per rappresentare il numero del giorno giuliano.
3. Modificatori (parametri modifier)
Il valore time-value può essere seguito da zero o più modifier che alterano la data e/o ora. I modificatori sono applicati a partire da sinistra a destra. I modificatori disponibili sono:
1. NNN days
2. NNN hours
3. NNN minutes
4. NNN.NNNN seconds
5. NNN months
6. NNN years
7. start of month
8. start of year
9. start of day
10. weekday N
11. unixepoch
12. localtime
13. utc
I modificatori da 1 a 6 semplicemente aggiungono la quantità di tempo specificata alla data/ora. Il carattere ‘s’ alla fine del modificatore è opzionale.
Notare che il risultato viene “normalizzato”, ad esempio:
• la data ‘2001-03-31’ modificata da '+1 month' inizialmente dà come risultato 2001-04-31, ma Aprile ha 30 giorni cosicchè la data è normalizzata a ‘2001-05-01’
• 29 febbraio di un anno bisestile e il modificatore è ‘±N years’ con N non multiplo di 4 restituisce come data il I marzo, ad es.:
SELECT date('2020-02-29', '+2 year'); restituisce: 2022-03-01
Mentre
SELECT date('2020-02-29', '+4 year'); restituisce: 2024-02-29
I modificatori da 7 a 9, "start of", spostano la data/ora indietro, all’inizio del corrente mese, anno, giorno.
Il modificatore "weekday" (10) sposta la data in avanti, se necessario, alla prossima data il cui numero di giorno della settimana è pari a N (0 domenica, 1 lunedì, …). Se la data corrisponde già al giorno indicato viene lasciata inalterata.
Il modificatore "unixepoch" (11) funziona solo se applicato a un valore espresso nel formato DDDDDDDDDD. Indica che il valore DDDDDDDDDD non deve essere interpretato come un Julian day bensì come Unix Time ( numero di secondi a partire dal 01-01-1970).
Il modificatore "localtime" (12) presuppone che time-value sia un valore
Universal Coordinated Time (UTC) e aggiusta tale valore al localtime.
Il modificatore "utc" (13) è l’opposto del “localtime”: presuppone che il time-value sia un valore localtime e lo converte nel tempo UTC.
4. Esempi
SELECT date('now'); data corrente.
SELECT date('now','start of month', '+1 month', '-1 day'); ultimo giorno del mese corrente.
SELECT datetime(1092941466, 'unixepoch'); datetime corrispondente a unix timestamp 1092941466.
SELECT datetime(1092941466, 'unixepoch', 'localtime'); datetime corrispondente a unix timestamp 1092941466 con conversione in localtime.
SELECT strftime('%s','now'); unix timestamp corrente (numero di secondi a partire dal 01-01-1970).
SELECT julianday('now') - julianday('1776-07-04'); numero di giorni a partire dal giorno della US Declaration of Independence.
SELECT strftime('%s','now') - strftime('%s','2004-01-01 02:34:56'); numero di secondi a partire da un particolare momento del 2004.
SELECT date('now','start of year','+9 months','weekday 2'); data del primo martedì di ottobre del corrente anno.
SELECT datetime(2440587.5): restituisce “1970-01-01 00:00:00” (unix epoch).
SELECT (julianday('now') - 2440587.5)*86400.0; tempo a partire da unix epoch (“1970-01-01 00:00:00”) in seconds (come strftime('%s','now') tranne per il fatto che include anche la parte decimale).
Glossario
ISO-8601: standard internazionale per la rappresentazione di date e ore.
UTC: Il tempo coordinato universale o tempo civile, coincide con l'ora media di Greenwich, sobborgo di Londra, (GMT: Greenwich Mean Time è la sigla che identificava il fuso orario di riferimento della Terra. Dal 1º gennaio 1972 si utilizza appunto il Tempo coordinato universale (UTC)). Tutti gli altri fusi orari del pianeta sono definiti relativamente al tempo UTC, con un numero intero positivo per un fuso orario in anticipo rispetto all'orario UTC e con un numero intero negativo per un fuso orario in ritardo rispetto all'orario UTC. L’orario UTC è detto anche “zulu time”, in quanto il fuso orario di Greenwich è identificato con la lettera “Z” e questa lettera nell’alfabeto fonetico militare è detta “Zulu”.
L'orario in Italia è pari a UTC+1 (ora solare, autunno/inverno) oppure UTC+2 (ora legale, primavera/estate).
Julian day: Si intende il 1º gennaio 4713 a.C. secondo il calendario giuliano, ossia quello valido per le date anteriori al 15 ottobre 1582. La data corrispondente usando retroattivamente il calendario gregoriano sarebbe il 24 novembre 4714 a.C. (B.C. = Before Christ, equivalent a A.C.)
Proleptic Gregorian calendar: estensione del calendario gregoriano alle date antecedenti il 15 ottobre 1582 (giorno dell’introduzione ufficiale del calendario gregoriano).
Anno solare: periodo che intercorre tra due passaggi successivi del sole allo zenit dello stesso tropico; durata 365 giorni, 5 ore, 48 minuti, 46 secondi.
Differenza con il calendario giuliano: l’anno giuliano risulta più lungo di 11 minuti e 14 secondi; tenendo conto dell’introduzione dell’ anno bisestile ogni 4 anni questo porta alla differenza di 1 giorno ogni 128 anni e di una settimana ogni 896: l’anno giuliano è più avanti di 1 giorno rispetto all’anno solare ogni 128 anni. Per ovviare a questa discrepanza il papa Gregorio XIII nel 1582 introdusse il calendario detto appunto Gregoriano: il giorno successivo a giovedì 4 ottobre 1582 fu il venerdì 15 ottobre 1582 per recuperare i 10 giorni di differenza accumulati nei secoli tra calendario giuliano e calendario solare. Le date per l’introduzione del nuovo calendario (4 ottobre – 15 ottobre) furono scelte perché in questo intervallo non ci sono delle feste solenni.
Differenza tra calendario giuliano e calendario gregoriano:
• calendario giuliano: sono bisestili tutti gli altri anni divisibili per 4.
• calendario gregoriano: gli anni secolari (anni divisibili per 100: 1600, 1700, 1800, 1900, 2000, 2100, … ) sono bisestili solo se divisibili per 400. Sono bisestili tutti gli altri anni divisibili per 4. Pertanto in 400 anni nel calendario gregoriano sono bisestili 97 giorni contro i 100 del calendario giuliano: la differenza di 11 minuti e 14 secondi tra anno giuliano e anno solare, sulla media dei 400 anni, si riduce a 26 sec che significa lo sbalzo di 1 giorno dopo 30 secoli (3000 anni).
1 g. = 86400 sec.
Anno giuliano = 365 gg. ; con l’introduzione dell’anno bisestile ogni 4 anni allora anno giuliano medio = 365 gg. 6 ore
Anno solare = 365 gg. 5 ore 48 minuti 46 sec
Differenza anno giuliano – anno solare = 11 min., 14 sec = 674 sec/anno
Dopo 128 anni l’anno giuliano è avanti rispetto all’anno solare di 1 g. (86400/674)
Dopo 400 anni l’anno giuliano è avanti rispetto all’anno solare di 269600 sec. (3,12 gg.).
Nell’arco di 400 anni, nel calendario giuliano si hanno 100 gg. bisestili, mentre nel calendario gregoriano i giorni bisestili sono 97, quindi nel calendario gregoriano sui 400 anni la differenza con l’anno solare sarà 3,12 – 3 = 0,12gg. = 0,12*86400 sec. = 10368 sec per 400 anni 10368/400 = 25,92 sec all’anno.
