[pillola] DATE/TIME e MySQL. PARTE PRIMA Gestione dati DATE/TIME con MySQL. Premessa. La fonte di riferimento e' il manuale ufficiale MySQL. Questa documentazione, e' utilizzata per on-the-job training su MySQL. E' una semplice documentazione della gestione DATE/TIME in MySQL. E' un documento che si rivolge a chi inizia, a chi ha difficolta' di traduzione, a chi utilizza MySQL ed ha necessita' di gestire DATE/TIME nelle sue tabelle. Gli utenti esperti sono cortesemente invitati a dare suggerimenti su come migliorare e integrare il documento. Tutte le query sono state testate e funzionanti su una suite composta da: Apache2.0.44 - PHP 4.3.1 - MySQL 4.0.12 - OS WIN 98/2000/xp. Sono possibili errori di copia incolla. Potete verificare le funzioni provando gli script php che troverete piu' avanti. Non necessita di db, ma solo della connessione a MySQL. MySQL e' conforme alle specifiche dell'anno 2000. (ecco fatto, l'ho detto) MySQL e la gestione dei campi formato DATE/TIME. Tutti i campi relativi a data/ora hanno una gamma di valori consentiti oppure una serie di zero se la data/ora non viene indicata oppure inserita con un formato non valido. Non ci sono segnalazioni di errore per inserimento data/ora non valida. I campi in formato data/ora su MySQL quindi NON sono mai EMPTY o NULL ma conterranno sempre una data/ora valida oppure degli 0 (zero) composti nel formato data/ora previsto nel campo. Se si cerca di estrarre da un campo dei dati esistenti ma non congruenti con la richiesta (es. cercare di estrarre un valore di data da campo formato time) la query restituira' un NULL, oppure un valore 00:00:00. Non fanno parte dei campi formato data/ora i formati campo numerici o char/varchar. Parrebbe banale, ma in realta' MySQL e' flessibile e, se i campi numerici o char/varchar contengono dei dati in formato compatibile ai campi data/time, permette di applicare con successo le funzioni data/ora anche su questi campi. Se il contenuto del campo non e' congruente al formato date/time richiesto la funzione data/time rendera' NULL oppure un risultato fantasioso. In altre parole: se i vari campi contengo dati in formato data/time compatibile, MySQL trattera' questi dati alla stregua di quelli inseriti nei campi con formato date/time. Il controllo dei valori avviene sul formato del dato inserito nel campo. Tanto vale quindi utilizzare dei campi dedicati e non i campi in formato numerico o char/varchar per gestire date/time. Quanto meno MySQL esegue su questi formati sommari controlli sui valori inseriti. Dei due: O son validi oppure sono zeri. Controlli sommari, perche'. MySQL non esegue il controllo della congruenza del giorno/mese. La data del 30 febbraio e' valida come le altre. MySQL ritiene validi i range: giorni 1-31, i mesi 1-12, gli anni 1000-9999. Per TIME: i secondi ed i minuti col canonico 00-59, mentre per le ore si oscilla tra -838:59:59 e 838:59:59. Il formato dei dati inseriti e' sempre YYYY-MM-DD hh:mm:ss oppure numerico se inserito in campo formato TIMESTAMP o in campo frm. numerico. MySQL riconosce, pero', anche altri tipi di separatori per data/time, come + ^ % & ed altri, compreso lo spazio e nessun separatore (formato numerico).Ma sempre memorizzera' nei formati descritti. Nel campo frm. char/varchar sara' una semplice stringa. Bisogna prestare attenzione ai separatori non standard e fare delle prove, alcuni separatori di data sono accettati senza che il valore sia racchiuso tra apici, altri separatoriinvece devono essere inseriti come se la data fosse una stringa: "2003/05/20". Verranno trasformati tutti in " - " per le date e in " : " per le ore. Se e' presente uno spazio es: 2003-05-20 12:00:00 la data deve essere tra apici cioe' "2003-05-20 12:00:00" o verra' generato un errore di query. Usando PhpMyAdmin si potrebbero avere (e si hanno) risultati inattesi utilizzando separatori fantasiosi. Bisogna inoltre, prima di eseguire un update guidato, cancellare nel form di immissione il vecchio valore, oppure la funzione applicata leggera' il campo esistente e lo passera' come argomento generando un errore. Altro incoveniente utilizzando PhpMyAdmin e' il caso di un update di un campo con valore identico all'esistente e con presenza nella tabella di un campo formato TIMESTAMP. Se non ci sono righe da modificare non si deve aggiornare il valore del campo frm. TIMESTAMP. PhpMyAdmin invece aggiorna il campo e restituisce una riga modificata. Questo non succede immettendo i valori dalla finestra QUERY. Nelle query di esempio utilizzeremo UPDATE, ovviamente lo stesso construtto di inserimento e' valido anche per INSERT. Vediamo DATE/TIME nel dettaglio dei formati di campo : Occupazione di memoria dei formati date/time: code: +-------------------+ | Tipo | Memoria | |---------|---------| |YEAR | 1 byte | |DATE | 3 bytes | |TIME | 3 bytes | |DATETIME | 8 bytes | |TIMESTAMP| 4 bytes | +-------------------+ YEAR(4) - Registra l'anno e occupa un solo byte. I valori validi oscillano tra il 1901 e il 2155. I valori possono essere specificati con 1, 2 oppure 4 cifre. In ogni caso i dati sono memorizzati con 4 cifre. Con 1 cifra si intendono gli anni dal 2000 al 2009. Con 2 cifre: da 00 a 69 verranno interpretati come dal 2000 al 2069, da 70 a 99 come anni dal 1970 al 1999. Per gli altri anni i valori ammessi (dal 1901 al 1969 e dal 2069 al 2155) dovranno essere inseriti con 4 cifre esplicite. Le funzioni che permettono di inserire l'anno corrente sono NOW() SYSDATE(), CURRENT_TIMESTAMP che sono equivalenti tra loro e contengono i valori data/ora, oppure CURRENT_DATE, CURDATE() anche essi equivalenti tra di loro ma che contengono solo la data. CURRENT_TIMESTAMP e CURRENT_DATE possono avere o meno le parentesi(). Il manuale non riporta parentesi ma non ci sono controindicazioni ad usarle. (VOID) Query: UPDATE tabella SET anno = NOW() where tua_condizione; UPDATE tabella SET anno = CURRENT_DATE where tua_condizione; UPDATE tabella SET anno = 2003 where tua_condizione; DATE - Registra l'evento come anno - mese - giorno. Non tiene conto dell'ora. I valori immessi possone essere dal 1000-01-01 al 9999-12-31. Si utilizza per memorizzare una data con l'immissione di una data esplicita, tramite una chiamata alla funzione NOW() o equivalenti, oppure a CURRENT_DATE, CURDATE(). Nel menu' di PhpMyAdmin si trovano NOW() e CURDATE(). La funzione NOW() porta in se anche hh:mm:ss che verranno omessi (ignorati). Attenzione: i dati espliciti andranno forniti nel formato previsto di yyyy-mm-dd. Inserendo la data nel formato dd-mm-yyyy si otterranno date impreviste. Esempio: Inserendo la data 18-05-2003 otterremo la data 2018-05-20 perche' verra' interpretato 18 come anno a due cifre, 05 mese e 20 giorno, escludendo 03 perche' eccedente. Con questo ultimo esempio si e' chiarito che la data puo' essere immessa anche nel formato "yy-mm-dd", oppure anche 3-5-18 che diventeranno nel campo 2003-05-18. MySQL accetta anche il formato numerico "20030519" che diverra' 2003-05-19. In questo caso la italica data 19052003 (19-05-2003) diventera' invece 0000-00-00 e non 1905-20-03 perche' la rappresentazione del mese (20) e' fuori range mese 1-12. Query: UPDATE tabella SET data = NOW() where tua_condizione; UPDATE tabella SET data = CURRENT_DATE() where tua_condizione; UPDATE tabella SET data = 2003-05-20 where tua_condizione; UPDATE tabella SET data = "2003/05/20" where tua_condizione; TIME - Il tipo di dato TIME si occupa di memorizzare l'ora senza occuparsi minimamente della data. Anche per l'ora si puo' utilizzare, come per date, dei formati espliciti hh:mm:ss oppure numerici hhmmss. Come detto in precedenza il range ammesso per le ore va da -838 a 838 mentre minuti e secondi da 00 a 59. Attenzione: MySQL legge le ore da destra verso sinistra se immettete l'ora senza separatori, mentre la legge da sinistra verso destra se ci sono i separatori. Quindi se mettete l'ora in questo modo (6 digit): 001224 troverete 00:12:24, ma se inserite l'ora con solo 4 digit: 1224 troverete 00:12:24, se invece inserite l'ora incompleta ma con il separatore es.: 12:24 troverete 12:24:00. Attenzione ad un altro particolare: se doveste utilizzare l'ora memorizzata nel database per i vostri scopi, sappiate che incontrando l'ora 00:00:00 non saprete mai se e' cosi' a causa di ora non valida o se perche' erano effettivamente le ore 00:00:00. A differenza della data 0000-00-00 non esistente, l'ora 00:00:00 e' un'ora potenzialmente valida. Le funzioni che permettono di inserire l'ora di sistema sono NOW(), i suoi simili e CURRENT_TIME o CURTIME(). CURRENT_TIME puo' avere o meno le parentesi() UPDATE tabella SET time = NOW() where id=$id; UPDATE tabella SET time = CURRENT_TIME() where id=$id; UPDATE tabella SET time = 12:00:00 where id=$id; DATETIME - NON deve essere considerato l'unione di DATE e TIME appena visti, ma un formato diverso. (TIME potrebbe avere un valore non ammesso in DATETIME). Data e time hanno qui una valenza globale. Una somma di ore che superi le 24 incrementa di un giorno la data. In questo caso il range di valori ammessi va dall'anno 1000-01-01 00:00:00 al 9999-12-31 23:59:59. L'inserimento manuale dei dati deve rispettare il formato previsto per i tipi DATA e TIME gia' descritti. Le funzioni che permettono di inserire data/ora di sistema sono NOW() ed i suoi simili e CURRENT_DATE o CURDATE(). In questo ultimo caso, CURRENT_DATE e CURDATE() non possiedono i dati dell'ora corrente. Ora, minuti, secondi saranno 00:00:00, mentre invece CURTIME() generera' un errore cioe', tutti zero (0000-00-00 00:00:00). UPDATE tabella SET dataora = NOW() where id=$id; UPDATE tabella SET dataora = '2003-05-19 12:00:00' where id=$id; nota: in questo ultimo esempio serve raggruppare il valore con apici a causa dello spazio presente tra data e ora. TIMESTAMP - Per ultimo il campo formato TIMESTAMP. L'intervallo di tempo gestibile va dall'anno 1970-01-01 al 2037-12-31. con massima risoluzione al secondo. Vengono utilizzati sempre 4 bytes. Il dato viene memorizzato in modo numerico: yyyymmddhhmmss per un totale di 14 digit. E importante notare che il valore ricavato con TIMESTAMP e' SEMPRE di 14 digit, anche se nel campo abbiamo diversi modi per visualizzarlo. Vediamo: code: +--------------+----------------+ | Column type | Display format | |--------------+----------------| | TIMESTAMP(14)| YYYYMMDDHHMMSS | | TIMESTAMP(12)| YYMMDDHHMMSS | | TIMESTAMP(10)| YYMMDDHHMM | | TIMESTAMP(8) | YYYYMMDD | | TIMESTAMP(6) | YYMMDD | | TIMESTAMP(4) | YYMM | | TIMESTAMP(2) | YY | +-------------------------------+ Si puo' quindi ridurre la visibilita' della data secondo lo schema qui sopra riportato, ma questo non significa ridurre lo spazio di memoria occupato o eliminare i dati in eccesso alla visibilita' impostata. Se si imposta la colonna ad 8 digit si vedranno solo YYYYMMDD, ma operazioni su hhmmss saranno ugualmente effettuate. Allargando poi la visibilita' con ALTER TABLE da 8 a 14 digit ricompariranno i valori non visibili in precedenza. Il tipo di formato TIMESTAMP puo' essere aggiornato in modo esplicito con date/time rispettando il numero dei digit sopra indicati. Inserendo per es.: 20030512 verra' aggiornata solo la data, posizionando a 00:00:00 il tempo. Oppure puo' essere usata la funzione NOW() e suoi alias. Se il campo e' settato NULL e viene omesso nella query, verra' automaticamente aggiornato alla data/ora corrente in concomitanza di qualsiasi UPDATE che modifichi la tupla. Ci possono essere vari campi formato TIMESTAMP nella stesso record, ma solo il PRIMO in ordine di posizione nella tabella verra' automaticamente aggiornato con UPDATE. Gli altri campi se non esplicitamente indicati con ora/time o funzione NOW() verranno trascurati e rimarranno con la data, se presente, inalterata. Un UPDATE che ribatte un valore gia' esistente e quindi con risultato zero righe modificate, non aggiorna automaticamente il campo col formato dati TIMESTAMP. Predisponendo quindi due campi TIMESTAMP, es.:il primo "last_update" ed il secondo "data_insert", all'INSERT della tupla (compreso LOAD DATA INFILE) verranno automaticamente popolati entrambi dalla funzione NOW(), mentre i successivi UPDATE la funzione NOW() aggiornera' solo il primo campo TIMESTAMP incontrato lasciando inalterato il campo successivo. Ecco quindi funzionanti senza una riga di software due colonne molto significative. (Data ultimo aggiornamento - Data inserimento). Pag 1 - SEGUE ... Bibliografia: http://www.mysql.com/doc/en/index.html