Come promesso qualche settimana or sono, dopo aver descritto la manipolazione di date e tempo con PHP nell'articolo Date in PHP: come gestirle, affrontiamo il medesimo argomento dal punto di vista del database MySQL.
In queste pagine analizzeremo i diversi formati disponibili per archiviare e trattare tale tipo di informazioni, un successivo articolo sarà invece dedicato alla descrizione delle principali funzioni per la gestione delle date.
Premetto che il linguaggio utilizzato sarà piuttosto informale, non me ne vogliano i puristi. Ad esempio anzichè ricorrere a termini quali database management system o tupla mi riferirò più correntemente a database o riga.
Prima di iniziare la trattazione è bene segnalare che nella versione 4.1 e successive sono state introdotte alcune modifiche, in particolare relativamente al tipo TIMESTAMP, di seguito descritto. Per brevità mi limiterò ad accennarle dove lo reputi opportuno. Farò inoltre riferimento ad un'installazione standard del server MySQL, senza particolari settaggi delle variabili di sistema.
Tipi di campo MySQL per date e tempo
Cominciamo descrivendo i tipi di campo, o di colonna – se preferite – che MySQL ci offre per memorizzare informazioni relative al tempo, questi sono:
- DATE
- DATETIME
- TIME
- TIMESTAMP
- YEAR
DATE: il dato viene visualizzato nel formato AAAA-MM-GG, ovvero l'anno espresso mediante 4 cifre, il mese espresso con 2 cifre e lo stesso dicasi per il giorno. Il range di valori va' da '1000-01-01' a '9999-12-31' e sono necessari 3 byte per la memorizzazione.
DATETIME: analogo al precedente aggiunge peró informazioni su ore minuti e secondi. Archivia il dato nella forma AAAA-MM-GG OO:MM:SS, dove si nota la rappresentazione rispettivamente di ore (OO), minuti (MM) e secondi (SS) mediante 2 cifre. L'intervallo permesso è '1000-01-01 00:00:00', '9999-12-31 23:59:59'. In questo caso lo spazio richiesto per la memorizzazione è pari a 8 byte.
TIME: il formato è OO:MM:SS, con un intervallo che va da '-8838:59:59' a '839:59:59' e con un ingombro di 3 byte. Se i valori possono sembrare strani tenete conto che possono rappresentare il risultato di operazioni matematiche sulle ore.
TIMESTAMP[(dimensione)]: questo tipo di campo, memorizzato in 4 byte, risulta utile per determinare automaticamente l'istante in cui un certo record è stato inserito o modificato. Se la tabella contiene più campi TIMESTAMP solo il primo sarà automaticamente aggiornato (dalla versione 4.1.2 di MySQL si può specificare quale colonna aggiornare). Per ottenere tale automatismo sarà sufficiente non specificare nelle istruzioni di INSERT o UPDATE il valore per la colonna in oggetto oppure impostarla a NULL. Se quanto esposto vi sembra un po' oscuro non preoccupatevi perché; chiariremo i concetti nei successivi esempi.
Il formato di visualizzazione del TIMESTAMP varia a seconda del valore specificato per "dimensione" come riportato nella seguente tabella considerando la data del 20 maggio 2005 19:11:02:
| definizione | visualizzazione | esempio |
| TIMESTAMP(14) | AAAAMMDDOOMMSS | 20050520191102 |
| TIMESTAMP(12) | AAMMDDOOMMSS | 050520191102 |
| TIMESTAMP(10) | AAMMDDOOMM | 0505201911 |
| TIMESTAMP(8) | AAAAMMDD | 20050520 |
| TIMESTAMP(6) | AAMMDD | 050520 |
| TIMESTAMP(4) | AAMM | 0505 |
| TIMESTAMP(2) | AA | 05 |
| TIMESTAMP | AAAAMMDDOOMMSS | 20050520191102 |
A partire dalla versione 4.1.0 la rappresentazione è stata uniformata a DATETIME ed eliminato il supporto per la dimensione. In dichiarazioni del tipo TIMESTAMP(2) la dimensione specificata verrà ignorata. La documentazione ufficiale, come già accennato, riporta in dettaglio tutte le modifiche intervenute.
YEAR[(2|4)]: questo campo viene utilizzato per rappresentare un anno mediante 2 o 4 cifre a seconda della dichiarazione, 4 è il valore di default. Lo spazio dedicato per la memorizzazione è pari comunque ad 1 byte. I valori consentiti vanno dal 1901 al 2155 per la rappresentazione a quattro cifre. Nell'altro caso i numeri tra 00 e 69 saranno interpretati come 2000-2069, mentre quelli tra 70 e 99 come 1970-1999. Si tenga presente che tale tipo di interpretazione viene seguita da MySQL in tutte le altre circostanze in cui l'anno venga rappresentato mediante due sole cifre.
Utilizzare il timestamp
Vediamo ora alcuni esempi che possano chiarire l'utilizzo di un campo timestamp. Come primo passo, immaginando di essere al prompt dei comandi di MySQL, creiamo una semplice tabella ed inseriamo alcuni record:
mysql>CREATE TABLE prova (
id TINYINT (3) UNSIGNED NOT NULL AUTO_INCREMENT,
nome VARCHAR (30),
cognome VARCHAR (30),
tstamp TIMESTAMP,
PRIMARY KEY(id)
);
mysql> INSERT INTO prova (nome,cognome) VALUES ('mario','rossi');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO prova VALUES ('','guido','bianchi',NULL);
Query OK, 1 row affected (0.00 sec)
mysql> SELECT * FROM prova;
+----+-------+---------+----------------+
| id | nome | cognome | tstamp |
+----+-------+---------+----------------+
| 1 | mario | rossi | 20050521183614 |
| 2 | guido | bianchi | 20050521183706 |
+----+-------+---------+----------------+
2 rows in set (0.00 sec)
Come si può notare nella prima query abbiamo ignorato il campo id, in quanto definito auto_increment e quindi generato automaticamente, ma quel che più conta è che abbiamo tralasciato anche il campo "tstamp". Il risultato, visualizzato mediante la SELECT, mostra che MySQL ha provveduto a riempirlo con il valore corrispondente all'istante d'inserimento del record: 20050521183614. Tale valore si interpreta facilmente come 21-05-2005 18:36:14 (2005/05/21/18/36/14).
Il medesimo effetto si ottiene con la seconda query dove abbiamo specificato un valore NULL per il campo "tstamp".
Aggiorniamo ora i record inseriti:
mysql> UPDATE prova SET nome = 'gino' WHERE id = 1; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE prova SET nome = 'guido' WHERE id = 2; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> SELECT * FROM prova; +----+-------+---------+----------------+ | id | nome | cognome | tstamp | +----+-------+---------+----------------+ | 1 | gino | rossi | 20050521185307 | | 2 | guido | bianchi | 20050521183706 | +----+-------+---------+----------------+ 2 rows in set (0.00 sec)
Notiamo subito che nel primo caso MySQL tiene traccia dell'istante di modifica inserendo nel campo "tstamp" il valore 20050521185307, da cui si evince che l'operazione è stata effettuata nella medesima data, ma all'orario 18:53:07. Nel secondo caso il valore rimane immutato, 20050521183706, questa apparente stranezza si deve al fatto che, a ragione, MySQL non considera aggiornato un record in cui si è inserito un valore identico al valore corrente.
Supponiamo di voler tenere traccia solo dell'istante d'inserimento del record e non delle successive modifiche; una possibile soluzione consiste nell'imporre che il campo sia uguale a se stesso nelle successive query d'aggiornamento:
mysql> UPDATE prova SET nome = 'pino', tstamp = tstamp WHERE id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM prova; +----+-------+---------+----------------+ | id | nome | cognome | tstamp | +----+-------+---------+----------------+ | 1 | pino | rossi | 20050521185307 | | 2 | guido | bianchi | 20050521183706 | +----+-------+---------+----------------+ 2 rows in set (0.00 sec)
Se vogliamo memorizzare sia l'istante d'inserimento che quello dell'ultima modifica possiamo procedere come di seguito descritto. Innanzitutto modifichiamo la tabella cambiando nome alla colonna da "tstamp" a "inserimento". Aggiungiamo una colonna "modifica" subito dopo il campo "cognome" di modo che, essendo il primo campo TIMESTAMP, venga automaticamente aggiornata da MySQL. Ricordo che dalla versione 4.1 si sarebbe potuto procedere diversamente. Per brevità d'ora in poi non riporterò negli esempi i messaggi di risposta del database relativi al buon fine delle query, al numero di righe interessate etc.
mysql> ALTER TABLE prova CHANGE tstamp inserimento TIMESTAMP(14); mysql> ALTER TABLE prova ADD modifica TIMESTAMP(14) AFTER cognome; mysql> SELECT * FROM prova; +----+-------+---------+----------------+----------------+ | id | nome | cognome | modifica | inserimento | +----+-------+---------+----------------+----------------+ | 1 | pino | rossi | 00000000000000 | 20050521185307 | | 2 | guido | bianchi | 00000000000000 | 20050521183706 | +----+-------+---------+----------------+----------------+
Se la versione del database server fosse 4.1 o superiore il primo ALTER TABLE richiederebbe di specificare un valore di default 0 altrimenti si creerebbe un conflitto aggiungendo un'ulteriore colonna di tipo timestamp.
Si noti come, modificando la struttura della tabella, i record precedentemente inseriti riportino una sequenza di zeri all'interno del campo "modifica", su questo ci soffermeremo però in un successivo paragrafo. Ora inseriamo un nuovo record e subito dopo modifichiamolo:
mysql> INSERT INTO prova (nome,cognome,inserimento)
VALUES ('mario','bianchi',NOW());
mysql> SELECT * FROM prova;
+----+-------+---------+----------------+----------------+
| id | nome | cognome | modifica | inserimento |
+----+-------+---------+----------------+----------------+
| 1 | pino | rossi | 00000000000000 | 20050521185307 |
| 2 | guido | bianchi | 00000000000000 | 20050521183706 |
| 3 | mario | bianchi | 20050523085542 | 20050523085542 |
+----+-------+---------+----------------+----------------+
mysql> UPDATE prova SET cognome = 'verdi' WHERE id = 3;
mysql> SELECT * FROM prova WHERE id = 3;
+----+-------+---------+----------------+----------------+
| id | nome | cognome | modifica | inserimento |
+----+-------+---------+----------------+----------------+
| 3 | mario | verdi | 20050523085829 | 20050523085542 |
+----+-------+---------+----------------+----------------+
Poichè la colonna "inserimento" si trova in seconda posizione, non verrebbe automaticamente aggiornata da MySQL, ma presenterebbe la solita sequenza di zeri. Per ovviare all'inconveniente abbiamo forzato il campo ad assumere il valore fornito dalla funzione NOW(), cioè l'istante corrente. Infatti, come si può verificare, dopo la prima query "inserimento" e "modifica" riportano il medesimo valore, 20050523085542, in corrispondenza del record con id pari a 3. I successivi aggiornamenti, senza dover specificare nulla, cambieranno in automatico l'istante di modifica lasciando inalterato l'istante d'inserimento.
La flessibilità di MySQL
Caratteristiche peculiari di MySQL sono la flessibilità e la capacità d'interpretazione delle query. Rispetto a database più rigorosi, risulta sicuramente più semplice da utilizzare anche se ci espone al rischio di risultati inattesi. Creiamo una nuova tabella prova2 per i successivi esperimenti:
mysql> CREATE TABLE prova2( id TINYINT (3) UNSIGNED NOT NULL AUTO_INCREMENT, data DATE, datatempo DATETIME, tempo TIME, tstamp TIMESTAMP, PRIMARY KEY(`id`) );
Nel caso dei campi data possiamo, ad esempio, utilizzare sia stringhe sia numeri anche se, per mia personale esperienza, in generale le stringhe sono da preferirsi. Tutte le query riportate daranno esito positivo inserendo il valore corretto '2005-05-22':
mysql> INSERT INTO prova2 (data) VALUES ('05-05-22');
mysql> INSERT INTO prova2 (data) VALUES (050522);
mysql> INSERT INTO prova2 (data) VALUES ('2005-05-22');
mysql> INSERT INTO prova2 (data) VALUES (20050522);
Ecco altri esempi di sintassi "rilassata" tutti correttamente interpretati:
mysql> INSERT INTO prova2 (data) VALUES ('05.05.22');
mysql> INSERT INTO prova2 (data) VALUES ('05/05/22');
mysql> INSERT INTO prova2 (data) VALUES ('05+05+22');
mysql> INSERT INTO prova2 (datatempo) VALUES ('05-05-22 11:16:33');
mysql> INSERT INTO prova2 (datatempo) VALUES ('05.05.22 11@16@33');
mysql> INSERT INTO prova2 (datatempo) VALUES ('05*05*22 11+16+33');
Le prime tre query inseriranno il valore '2005-05-22' nel campo "data" mentre le ultime tre il valore '2005-05-22 11:16:33' nel campo "datatempo". MySQL non si scompone neanche se cerchiamo di assegnare ad un campo DATE valori DATETIME o TIMESTAMP, o viceversa. Nel primo caso aggiungerà '00:00:00' per il tempo, nel secondo semplicemente eliminerà le informazioni in eccesso. Vediamo gli esempi:
mysql> INSERT INTO prova2 (data) VALUES ('2005.05.22 09:46:45');
mysql> INSERT INTO prova2 (data) VALUES ('20050522094645');
mysql> INSERT INTO prova2 (datatempo) VALUES ('2005-05-22');
mysql> INSERT INTO prova2 (tstamp) VALUES ('2005-05-22');
Con le prime due query verrà inserito il valore '2005-05-22' tralasciando le informazioni relative al tempo. Mentre con la terza e la quarta verranno inseriti rispettivamente i valori '2005-05-22 00:00:00' e 20050522000000. Come si sarà ormai immaginato il controllo sulla validità delle date risulta molto blando: semplificando possiamo dire che se l'anno va' da 1000 a 9999, il mese da 00 a 12, il giorno da 00 a 31 MySQL non si lamenterà. Dato che "non è tutt'oro quel che luccica", se vogliamo garantire la consistenza dei nostri dati, dovremo implementare i necessari controlli via codice PHP.
Il "valore zero" nel campo data
Per quanto difficile, vi sarà pure qualche circostanza in cui riusciremo a specificare un valore illegale o fuori range per un campo data. In tal caso MySQL reagisce con il cosiddetto valore zero, "zero value", ovvero converte il dato inesatto in un valore di default. La tabella seguente illustra come avviene la conversione:
| tipo campo | valore zero |
| TIMESTAMP | 00000000000000 |
| DATE | '0000-00-00' |
| DATETIME | '0000-00-00 00:00:00' |
| TIME | '00:00:00' |
| YEAR | 0000 |
Vediamo un semplice esempio:
mysql> INSERT INTO prova2 VALUES ('','xxx','xxx','xxx','xxx');
mysql> SELECT * FROM prova2 WHERE data = 0;
+----+------------+---------------------+----------+----------------+
| id | data | datatempo | tempo | tstamp |
+----+------------+---------------------+----------+----------------+
| 15 | 0000-00-00 | 0000-00-00 00:00:00 | 00:00:00 | 00000000000000 |
+----+------------+---------------------+----------+----------------+
Come si può notare dalla SELECT d'esempio ci si può riferire a tali valori sia specificandoli nel loro esatto formato, sia più velocemente utilizzando i valori 0 o '0'.
Conclusioni su MySQL date
Gli esempi riportati sono stati testati su MySQL 3.23.49 e 4.1.10, la visualizzazione dei risultati fa' riferimento alla versione 3.23 del database server. Qualora troviate qualche discrepanza o vogliate approfondire qualche argomento, come al solito la fonte principale d'informazioni rimane la documentazione ufficiale reperibile sul sito di MySQL. Concludo dandovi appuntamento sulle pagine di FreePHP per la descrizione delle principali funzioni che il database offre per la manipolazione di date e tempo.
