Funzioni di data e tempo con MySQL

20 marzo 2006

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. Per un panorama completo consiglio comunque uno sguardo a MySQL Reference Manual- Date and Time Functions.

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                              |
+--------------------------------------------------+

Se vuoi aggiornamenti su Funzioni di data e tempo con MySQL inserisci la tua e-mail nel box qui sotto:
 
X
Se vuoi aggiornamenti su Funzioni di data e tempo con MySQL

inserisci la tua e-mail nel box qui sotto:

Ho letto e acconsento l'informativa sulla privacy

Acconsento al trattamento di cui al punto 3 dell'informativa sulla privacy