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:

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() DAY()
• ore, minuti e secondi
  questi dati possono essere estrapolati da informazioni orarie usando le funzioni HOUR() MINUTE() 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 DAYOFYEAR

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:

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

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

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() DATE_FORMAT() link

I metacaratteri più comunemente usati sono: %H %h %i %S %s

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 DATE_ADD

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

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