Dopo qualche settimana eccoci ancora qui a parlare di date e MySQL. Ci eravamo lasciati con l'articolo Memorizzare date e tempo con MySQL relativo ai tipi di campo disponibili per l'archiviazione di informazioni temporali. Nelle pagine che seguono considererò acquisiti tali argomenti e non mi soffermerò in spiegazioni dettagliate. Se non vi sentite sicuri seguite il soprammenzionato link e perdete qualche minuto nella lettura dell'articolo.
MySQL offre svariate funzioni per manipolare date e tempo, di seguito ne analizzeremo solo alcune. La scelta di quali privilegiare nella trattazione è frutto semplicemente della personale esperienza d'utilizzo.
La formattazione delle date
Sebbene possa sembrare un po' anomalo iniziare con questo argomento ritengo che, nel lavorare con le date, l'operazione statisticamente più comune sia estrarre alcuni valori dal database e presentarli in modo che siano facilmente interpretabili. Come descritto nel Memorizzare date e tempo con MySQL, la rappresentazione che MySQL adotta per valori DATE (AAAA-MM-GGH), DATETIME (AAAA-MM-GG OO:MM:SS) etc. risulta per noi poco familiare e leggibile. Di qui la necessità di elaborare il dato in modo da modificarne la visualizzazione. La funzione DATE_FORMAT() può risolvere il problema in maniera semplice ed elegante evitando inutili acrobazie con codice PHP.
Il suo comportamento è analogo a quello della funzione date() di PHP, descritta nell'articolo sulle Date in PHP: come gestirle. DATE_FORMAT riceve due argomenti: il primo è la data da formattare, il secondo una stringa composta da alcuni caratteri speciali, preceduti dal simbolo "%" (obbligatorio dalla versione 3.23), che indicano come tale data vada formattata.
Ecco un elenco parziale di tali caratteri, per la lista completa vi rimando alla documentazione ufficiale
.
| Specificatore | Descrizione |
| %d | giorno del mese numerico 00...31 |
| %M | nome del mese January...December |
| %m | mese numerico 00...12 |
| %H | ora 00...23 |
| %i | minuti 00...59 |
| %s | secondi 00...59 |
| %Y | anno di quattro cifre |
| %y | anno di due cifre |
Vediamo alcuni esempi per chiarire quanto descritto immaginando, al solito, di essere al prompt dei comandi di MySQL.
mysql> select DATE_FORMAT('2005-04-12 15:23:04','%d-%m-%Y %H:%i:%s') as 'data formattata';
+---------------------+ | data formattata | +---------------------+ | 12-04-2005 15:23:04 | +---------------------+
mysql> select DATE_FORMAT('2005-04-12 15:23:04','%d %M %y, ore: %H') as 'data formattata';
+----------------------+ | data formattata | +----------------------+ | 12 April 05, ore: 15 | +----------------------+
mysql> select DATE_FORMAT('20050412152304','giorno:%d,mese:%m,anno:%Y,ore:%H e %i') as 'data formattata';
+-----------------------------------------+ | data formattata | +-----------------------------------------+ | giorno:12,mese:04,anno:2005,ore:15 e 23 | +-----------------------------------------+
Come si nota basta utilizzare i caratteri speciali nella posizione desiderata per ottenere una stringa formattata secondo le proprie esigenze. Per semplicità di esposizione ho passato direttamente alla funzione la data da formattare, nulla cambierebbe se si trattasse di un valore estratto da una tabella. Immaginando di avere un campo DATETIME denominato "datainizio" in una tabella di nome "prova", una possibile query potrebbe essere così strutturata:
mysql> select DATE_FORMAT(datainizio,'%d-%m-%Y %H:%i:%s') as inizio FROM prova;
+---------------------+ | inizio | +---------------------+ | 01-11-2003 02:00:01 | | 01-10-2001 02:00:01 | | 01-01-2002 02:00:01 | +---------------------+
In questo modo visualizziamo tutti i valori memorizzati per il campo specificato e formattati come 'giorno-mese-anno ore:minuti:secondi', '%d-%m-%Y %H:%i:%s'.
Una piccola notazione: se decidessimo di ordinare i risultati in ordine crescente o decrescente, dovremmo fare attenzione al fatto che, formattando la data "alla maniera italiana", l'ordinamento delle stringhe può non coincidere con quello temporale, al contrario di quanto accade con la rappresentazione standard. La prima delle query che seguono non darà il risultato atteso al contrario della seconda.
mysql> select DATE_FORMAT(datainizio,'%d-%m-%Y %H:%i:%s') as inizio FROM prova ORDER BY inizio;
+---------------------+ | inizio | +---------------------+ | 01-01-2002 02:00:01 | | 01-10-2001 02:00:01 | | 01-11-2003 02:00:01 | +---------------------+
mysql> select DATE_FORMAT(datainizio,'%d-%m-%Y %H:%i:%s') as inizio FROM prova ORDER BY datainizio;
+---------------------+ | inizio | +---------------------+ | 01-10-2001 02:00:01 | | 01-01-2002 02:00:01 | | 01-11-2003 02:00:01 | +---------------------+
Per formattare informazioni relative al tempo si può ricorrere alla funzione TIME_FORMAT(), del tutto analoga a DATE_FORMAT, ma che utilizza solo gli specificatori di formato che manipolano ore, minuti e secondi.
Dalla versione 4.1.1 di MySQL è disponibile la preziosa funzione STR_TO_DATE(), inversa di DATE_FORMAT. Essa riceve come argomenti la stringa contenente la data e la corrispondente stringa di formattazione e restituisce un valore DATETIME, DATE o TIME a seconda delle circostanze.
mysql> select STR_TO_DATE('03/10/2005','%d/%m/%Y');
+--------------------------------------+
| STR_TO_DATE('03/10/2005','%d/%m/%Y') |
+--------------------------------------+
| 2005-10-03 |
+--------------------------------------+
mysql> select STR_TO_DATE('03.10.2005 12.33','%d.%m.%Y %H.%i');
+--------------------------------------------------+
| STR_TO_DATE('03.10.2005 12.33','%d.%m.%Y %H.%i') |
+--------------------------------------------------+
| 2005-10-03 12:33:00 |
+--------------------------------------------------+
MySQL NOW() - data e ora corrente
MySQL fornisce un piccolo gruppo di funzioni, se vogliamo banali, ma di uso molto comune. La funzione NOW() fornisce la data e l'ora corrente nel formato 'AAAA-MM-GG OO:MM:SS' o AAAAMMGGOOMMSS a seconda che si tratti di un contesto stringa o numerico:
mysql> select NOW();
+---------------------+ | NOW() | +---------------------+ | 2005-07-17 15:56:42 | +---------------------+
mysql> select NOW() + 0;
+-----------------+ | NOW() + 0 | +-----------------+ | 200507171555642 | +-----------------+
Nel secondo esempio aggiungendo 0 forziamo il risultato ad essere numerico.
Possono considerarsi sinonimi di NOW() le funzioni CURRENT_TIMESTAMP(), SYSDATE(), LOCALTIME(), LOCAL_TIMESTAMP(), le ultime due disponibili dalla versione 4.0.6.
Analoga, ma limitata alla data, è la funzione CURRDATE() o CURRENT_DATE(), mentre per il tempo vi sono CURRTIME() o CURRENT_TIME(). Ecco alcuni esempi d'utilizzo (si presti attenzione a dove si possano omettere le parentesi):
mysql> select CURDATE();
+------------+ | CURDATE() | +------------+ | 2005-07-17 | +------------+
mysql> select CURRENT_DATE();
+----------------+ | CURRENT_DATE() | +----------------+ | 2005-07-17 | +----------------+
mysql> select CURRENT_DATE;
+--------------+ | CURRENT_DATE | +--------------+ | 2005-07-17 | +--------------+
mysql> select CURTIME();
+-----------+ | CURTIME() | +-----------+ | 16:09:09 | +-----------+
mysql> select CURRENT_TIME();
+----------------+ | CURRENT_TIME() | +----------------+ | 16:09:09 | +----------------+
mysql> select CURRENT_TIME;
+----------------+ | CURRENT_TIME() | +----------------+ | 16:09:09 | +----------------+
Altra funzione che personalmente reputo molto utile è UNIX_TIMESTAMP(). In molte circostanze capita di effettuare elaborazioni in PHP che coinvolgano lo unix timestamp e l'uso di tale funzione rende agevole il passaggio di valori dal database all'applicazione. Per maggiori informazioni sul timestamp rimando all'articolo sulle Date in PHP: come gestirle.
Se non viene specificato alcun argomento UNIX_TIMESTAMP() fornisce lo unix timestamp corrente, se invece si specifica un valore di tipo DATE, DATETIME, TIMESTAMP fornisce lo unix timestamp corrispondente alla particolare data. Vediamola all'opera:
mysql> select UNIX_TIMESTAMP();
+------------------+ | UNIX_TIMESTAMP() | +------------------+ | 1121611168 | +------------------+
mysql> select UNIX_TIMESTAMP('2004-12-03 13:12:34');
+---------------------------------------+
| UNIX_TIMESTAMP('2004-12-03 13:12:34') |
+---------------------------------------+
| 1102075954 |
+---------------------------------------+
mysql> select UNIX_TIMESTAMP(20041203131234);
+--------------------------------+ | UNIX_TIMESTAMP(20041203131234) | +--------------------------------+ | 1102075954 | +--------------------------------+
Al contrario, per ottenere la rappresentazione di un valore timestamp come stringa del tipo 'AAAA-MM-GG OO:MM:SS' o numero AAAAMMGGOOMMSS (come al solito a seconda del contesto), basta ricorrere a FROM_UNIXTIME(). Tale funzione prevede come secondo argomento, opzionale, la consueta stringa di formattazione. Vediamo gli esempi chiarificatori:
mysql> select FROM_UNIXTIME(1102075954);
+---------------------------+ | FROM_UNIXTIME(1102075954) | +---------------------------+ | 2004-12-03 13:12:34 | +---------------------------+
mysql> select FROM_UNIXTIME(1102075954)+0;
+-----------------------------+ | FROM_UNIXTIME(1102075954)+0 | +-----------------------------+ | 20041203131234 | +-----------------------------+
mysql> select FROM_UNIXTIME(1102075954,'%d/%m/%Y ore:%H.%i.%s');
+---------------------------------------------------+ | FROM_UNIXTIME(1102075954,'%d/%m/%Y ore:%H.%i.%s') | +---------------------------------------------------+ | 03/12/2004 ore:13.12.34 | +---------------------------------------------------+
Un'ultima osservazione relativa alle funzioni che forniscono valori di data o tempo correnti. Se in una medesima query utilizziamo più volte una funzione come NOW(), non ci sono problemi perché il valore viene calcolato una sola volta all'inizio dell'esecuzione.
Operazioni matematiche con le date
Spesso ci si trova ad affrontare elaborazioni che riguardano addizioni e sottrazioni di date, a questo proposito cominciamo con l'analizzare la funzione PERIOD_ADD(). Essa aggiunge N mesi ad un fissato periodo espresso nel formato AAMM o AAAAMM, restituendo un valore AAAAMM. Vediamo ad esempio come aggiungere 10 mesi a marzo (03) 2000:
mysql> select PERIOD_ADD('200003',10);
+-------------------------+
| PERIOD_ADD('200003',10) |
+-------------------------+
| 200101 |
+-------------------------+
mysql> select PERIOD_ADD(200003,10);
+-----------------------+ | PERIOD_ADD(200003,10) | +-----------------------+ | 200101 | +-----------------------+
mysql> select PERIOD_ADD('0003',10);
+-----------------------+
| PERIOD_ADD('0003',10) |
+-----------------------+
| 200101 |
+-----------------------+
Il risultato 200101 si legge ovviamente come gennaio 2001. Per ottenere il numero di mesi tra due periodi possiamo ricorrere a PERIOD_DIFF(). Ad esempio il numero di mesi tra gennaio 2004 e luglio 2005 si ottiene dalla differenza tra 200507 e 200401 così:
mysql> select PERIOD_DIFF(200507,200401);
+----------------------------+ | PERIOD_DIFF(200507,200401) | +----------------------------+ | 18 | +----------------------------+
Se vogliamo calcolare il numero di mesi tra gennaio 2004 e luglio 2003 otterremo, ovviamente, un valore negativo:
mysql> select PERIOD_DIFF(200307,200401);
+----------------------------+ | PERIOD_DIFF(200307,200401) | +----------------------------+ | -6 | +----------------------------+
Per aggiungere un intervallo temporale ad una fissata data possiamo utilizzare DATE_ADD(). La sintassi della funzione è la seguente: DATE_ADD(data, INTERVAL espressione tipo). Dove al posto di "data" dovremo specificare un valore di partenza di tipo DATE o DATETIME. Nel secondo argomento "INTERVAL" è una parola riservata, "espressione" è una stringa che rappresenta il valore dell'intervallo da addizionare, mentre "tipo" indica la grandezza temporale cui si riferisce "espressione". Possibili valori per "tipo" sono YEAR, MONTH, WEEK, DAY, MINUTE, SECOND, la documentazione ufficiale riporta naturalmente l'elenco completo. Iniziamo con qualche esempio per fugare eventuali dubbi:
mysql> select DATE_ADD('2005-07-19',INTERVAL 5 DAY);
+---------------------------------------+
| DATE_ADD('2005-07-19',INTERVAL 5 DAY) |
+---------------------------------------+
| 2005-07-24 |
+---------------------------------------+
mysql> select DATE_ADD('2005-07-19', INTERVAL -5 DAY);
+-----------------------------------------+
| DATE_ADD('2005-07-19', INTERVAL -5 DAY) |
+-----------------------------------------+
| 2005-07-14 |
+-----------------------------------------+
mysql> select DATE_ADD('2005-07-19 08:49:10', INTERVAL 15 SECOND) as somma;
+---------------------+ | somma | +---------------------+ | 2005-07-19 08:49:25 | +---------------------+
mysql> select DATE_ADD('2005-07-19 08:49:10', INTERVAL '1:5' HOUR_MINUTE) as somma;
+---------------------+ | somma | +---------------------+ | 2005-07-19 09:54:10 | +---------------------+
mysql> select '2005-07-19' + INTERVAL 1 DAY;
+-------------------------------+ | '2005-07-19' + INTERVAL 1 DAY | +-------------------------------+ | 2005-07-20 | +-------------------------------+
La prima query aggiunge 5 giorni al 19 luglio 2005 fornendo come risultato il 24 luglio 2005. Nel secondo esempio, facendo precedere l'espressione dal simbolo "-", sottraiamo l'intervallo di 5 giorni. Il terzo e quarto esempio mostrano come aggiungere rispettivamente secondi ed ore e minuti a valori di tipo DATETIME. Accenno brevemente al fatto che dalla versione 3.23 del database la sintassi "INTERVAL espressione tipo" può essere utilizzata anche da sola come mostra l'ultimo degli esempi. Dopo qualche esperimento l'utilizzo della funzione dovrebbe risultarvi familiare.
Al posto di DATE_ADD possiamo utilizzare ADDDATE() che, con la sintassi ADDDATE(data, INTERVAL espressione tipo), si comporta esattamente nello stesso modo. Dalla versione 4.1.1 tale funzione ammette anche la sintassi ADDDATE(espressione, giorni) dove "espressione" rappresenta un valore DATE o DATETIME, mentre "giorni" sono i giorni da aggiungere.
mysql> select ADDDATE('2005-07-19 08:49:10', INTERVAL 15 SECOND);
+----------------------------------------------------+
| ADDDATE('2005-07-19 08:49:10', INTERVAL 15 SECOND) |
+----------------------------------------------------+
| 2005-07-19 08:49:25 |
+----------------------------------------------------+
mysql> select ADDDATE('2005-07-19 08:49:10', 10);
+------------------------------------+
| ADDDATE('2005-07-19 08:49:10', 10) |
+------------------------------------+
| 2005-07-29 08:49:10 |
+------------------------------------+
Nel secondo esempio abbiamo aggiunto 10 giorni alla data indicata.
Se si vuole sottrarre un intervallo temporale ad una fissata data basta ricorrere a DATE_SUB() la cui sintassi, DATE_SUB(data, INTERVAL espressione tipo), ormai non dovrebbe rappresentare un problema. Anche in questo caso SUBDATE() nella forma SUBDATE(data, INTERVAL espressione tipo) non è che un sinonimo della precedente, mentre SUBDATE(data, giorni) sottrae il valore "giorni" al valore "data" (sempre dalla versione 4.1.1). Chiudiamo con alcuni esempi:
mysql> select DATE_SUB('2005-07-19',INTERVAL 5 DAY);
+---------------------------------------+
| DATE_SUB('2005-07-19',INTERVAL 5 DAY) |
+---------------------------------------+
| 2005-07-14 |
+---------------------------------------+
mysql> select DATE_SUB('2005-07-19 08:49:10', INTERVAL '1:5' HOUR_MINUTE) as sottrazione;
+---------------------+ | sottrazione | +---------------------+ | 2005-07-19 07:44:10 | +---------------------+
mysql> select SUBDATE('2005-07-19 08:49:10', INTERVAL 15 SECOND) as sottrazione;
+---------------------+ | sottrazione | +---------------------+ | 2005-07-19 08:48:55 | +---------------------+
mysql> select SUBDATE('2005-07-19 08:49:10', 10);
+------------------------------------+
| SUBDATE('2005-07-19 08:49:10', 10) |
+------------------------------------+
| 2005-07-09 08:49:10 |
+------------------------------------+
Con la prima query sottraiamo 5 giorni al 19 luglio 2005, con la seconda e la terza rispettivamente 1 ora e 5 minuti e 15 secondi al 19 luglio 2005 08:49:10, l'ultima sottrae 10 giorni al medesimo valore.
Conclusioni su MySQL - date format
Al termine di questa breve carrellata di funzioni per la manipolazione delle date vorrei fare alcune considerazioni. Innanzitutto l'uso dei caratteri maiuscoli negli esempi ha l'unico scopo di mettere in rielievo alcune parti delle query. In altri termini usare subdate invece di SUBDATE o interval invece di INTERVAL non modificherebbe i risultati ottenuti.
Per ragioni di spazio non mi sono soffermato ad illustrare con esempi la nota flessibilità di MySQL, al contrario di quanto fatto nel precedente articolo. Si tenga presente, ad esempio, che funzioni che richiedono valori di tipo DATE accettano anche valori DATETIME ignorando le informazioni sul tempo. Attenti ad eventuali risultati inattesi, una query del tipo select '2005-12-01' + 1 anziché generare un errore fornirà comunque il risultato 2006, accettando una sintassi più "rilassata". Non vi resta che dedicare un po' di tempo agli esperimenti per meglio padroneggiare il comportamento del vostro database.
