Gestire date e orari

In una lezione precedente abbiamo visto i cinque tipi di dato che vengono usati in MySQL per gestire le informazioni temporali: DATE, TIME, DATETIME, TIMESTAMP e YEAR. In generale, comunque, MySQL esprime il formato della data come AAAA-MM-GG dove AAAA rappresenta l'anno scritto a quattro cifre, mentre MM e GG simboleggiano, rispettivamente, il mese ed il giorno. Il tempo viene trattato nel formato OO:MM:SS in cui i due punti separano, nell'ordine, le ore dai minuti e questi dai secondi.

In questa lezione vedremo, suddivise per tipologie, le principali funzioni che possono essere sfruttate all'interno dei comandi SQL per gestire informazioni temporali. Un elenco completo delle funzioni a disposizione è comunque disponibile sul sito ufficiale.

Impostare data e ora

La prima cosa che ci interessa imparare è inserire dati di uno dei tipi sopra elencati. Lo si può fare come segue:

insert into utenti (data) values ('2015-03-01');
insert into utenti (orario) values ('17:34');
insert into utenti (dataora) values ('2015-03-02 17:34');

In alternativa, per impostare le informazioni temporali attuali, si possono usare le seguenti funzioni:

Funzione	Descrizione
NOW()	restituisce data e ora attuali. Ammette i sinonimi CURRENT_TIMESTAMP() e CURRENT_TIMESTAMP
CURDATE()	restituisce data attuale. Ammette i sinonimi CURRENT_DATE() e CURRENT_DATE
CURTIME()	restituisce orario attuale. Ammette i sinonimi CURRENT_TIME() e CURRENT_TIME

Recuperare le informazioni

Una volta impostate, le informazioni data/ora possono essere lette, in tutto o in parte, prelevandone solo alcuni elementi. Per queste operazioni, esistono apposite funzioni:

• giorni, mesi e anni:
  molte funzioni permettono di recuperare queste informazioni da una data. Le più importanti sono YEAR(), MONTH() e DAY() che, rispettivamente, restituiscono anno, mese e giorno;
• ore, minuti e secondi:
  questi dati possono essere estrapolati da informazioni orarie usando le funzioni HOUR(), MINUTE() e SECOND();
• giorno nella settimana o nell'anno:
  può essere utile sapere a che giorno della settimana corrisponde una certa data. Tale informazione può essere ottenuta con DAYOFWEEK ed il risultato sarà un numero da 1 a 7, dove 1 corrisponderà alla domenica, 2 a lunedì e così via fino al 7, che corrisponde a sabato. Con la funzione DAYOFYEAR si otterrà un numero compreso tra 1 e 366 che indicherà il giorno dell'anno corrispondente alla data presa in considerazione.

Formattare date e orari

L'uso di un formato unico per esprimere date e orari è utile per gestire i dati dal punto di vista del programmatore, ma per mostrare l'output agli utenti è necessario formattare opportunamente questi dati. Lo si può fare con due funzioni: DATE_FORMAT() e TIME_FORMAT().

La funzione DATE_FORMAT() permette di esprimere il formato di una data usando una stringa costituita da appositi metacaratteri:

Metacarattere	Descrizione
%d	giorno del mese
%m	mese espresso in numero. La variante %M esprime il mese in parole
%Y	l'anno su quattro cifre. La variante %y esprime l'anno su due cifre

Un elenco completo dei codici da usare nell'espressione del formato è disponibile nella documentazione ufficiale.

Ecco alcuni esempi che mostrano vari modi per esprimere il 1° marzo 2015, direttamente sperimentabili nella console del comando mysql:

> SELECT DATE_FORMAT('2015-03-01','%d/%m/%Y');
01/03/2015
> SELECT DATE_FORMAT('2015-03-01','%d/%m/%y');
01/03/15
> SELECT DATE_FORMAT('2015-03-01','%d %M %Y');
01 March 2015

La funzione TIME_FORMAT() svolge lo stesso compito di DATE_FORMAT(), ma è riferita agli orari. I codici utilizzabili nell'espressione sono disponibili al medesimo link riportato in precedenza, ma si possono usare solo i formati orientati alla gestione dell'orario.

I metacaratteri più comunemente usati sono: %H per indicare le ore (da 0 a 24, in alternativa %h le mostra da 0 a 12), %i per i minuti e %S o %s per i secondi.

Alcuni esempi:

> SELECT TIME_FORMAT('17:25:34','%H-%i');
17-25
> SELECT TIME_FORMAT('17:25:34','%h:%i %p');
05:25 PM
> SELECT TIME_FORMAT('17:25:34','sono le %H e %i minuti');
sono le 17 e 25 minuti

Calcoli con date e orari

Spesso può essere utile saper svolgere operazioni con le date e gli orari, sommandoli o sottraendoli tra loro o con periodi di tempo costanti.

Per sommare un periodo di tempo ad una data o un orario si possono usare le funzioni ADDDATE e ADDTIME. La prima calcola la data derivante dalla somma tra il primo argomento, ed un intervallo di tempo espresso come INTERVAL espressione unità. Ad esempio:

> SELECT ADDDATE('2015-03-01',INTERVAL 5 DAY);
	   2015-03-06
	> SELECT ADDDATE('2015-03-01', 5);
	   2015-03-06

Come si vede, per sommare alla data cinque giorni si possono usare due espressioni diverse, INTERVAL 5 DAY o semplicemente il numero 5. Esiste una funzione sinonimo che si può usare, DATE_ADD, la quale però accetta solo la prima forma.

Discorso analogo vale per ADDTIME. Ecco direttamente qualche esempio:

> SELECT ADDTIME('17:25','05:05');
	   22:30:00
	> SELECT ADDTIME('17:25','00:05:05');
	   17:30:05

Nel caso di ADDTIME, si può indicare direttamente il lasso di tempo da sommare.

Un periodo può essere anche sottratto da una data. Esiste per questo motivo DATE_SUB il cui funzionamento è speculare a DATE_ADD:

> SELECT DATE_SUB('2015-03-01',INTERVAL 5 DAY);
2015-02-24

Infine, possiamo calcolare il periodo che intercorre tra due date con DATEDIFF. Questa funzione accetta due argomenti ed il risultato sarà un numero positivo se la prima data è successiva alla seconda, negativo altrimenti:

> SELECT DATEDIFF('2015-03-01','2015-02-10');
	   19
	> SELECT DATEDIFF('2015-01-01','2015-02-10');
	   -40