[Pillola] DATE/TIME e MySQL 4.1.x

[piero.mac]

[Pillola] DATE/TIME e MySQL 4.1.x

PARTE PRIMA

Aggiornamento della Gestione DATE/TIME con MySQL 4.1.

La fonte di riferimento e' il manuale ufficiale MySQL. E' un aggiornamento
della documentazione su DATE/TIME in MySQL 4.1.7, quindi trattera' solo le modifiche
e le nuove prestazioni ripetto alla precedente pillola su DATE/TIME di cui non e'
sostitutiva ma di solo aggiornamento.

Tutte le query sono state testate e funzionanti su una suite composta da:
Apache 2.0.47 - PHP 4.3.10 - MySQL 4.1.8 - OS WIN 2000/XP-SP2.

Premessa: Poiche' modifiche ed aggiornamenti si sono susseguite man mano nei
vari rilasci della versione 4.1, prenderemo in considerazione la prima versione stabile
rilasciata, la 4.1.7 e superiore. Per le versioni precedenti alla 4.1.7 fate
riferimento al manuale.

Rif.: http://dev.mysql.com/doc/mysql/en/da...functions.html

MySQL 4.1 e la gestione dei campi formato DATE/TIME.

Vediamo quindi subito eventuali cambiamenti al formato dei campi data:

rif.: http://dev.mysql.com/doc/mysql/en/Da..._overview.html


YEAR
Registra l'anno e occupa un solo byte. I valori validi oscillano tra
il 1901 e il 2155. Uguale alla versione precedente.

rif.: http://dev.mysql.com/doc/mysql/en/YEAR.html


TIME
Registra ora : minuti : secondi. Il range ammesso va da -838 a 838 per le ore,
mentre per minuti e secondi da 00 a 59. Somma le ore percio' dope le 24 continuera'
con 25, 26, eccetera... Uguale alla versione precedente.


DATE
Il range ammesso e' da: '1000-01-01' a '9999-12-31'. Il campo viene memorizzato
come prima in YYYY-MM-DD e sono accettati sia formati numerici (es.: 20050101)
che formati stringa (es.: '2005/01/01'). Da sottolineare che il separatore puo'
essere qualunque, ma il dato andra' passato come stringa, cioe' tra apici.
Uguale alla versione precedente.

Rif. valido anche per DATETIME e TIMESTAMP: http://dev.mysql.com/doc/mysql/en/DATETIME.html


DATETIME
Il range ammesso e' il solito, da: 1000-01-01 00:00:00 al 9999-12-31 23:59:59.
Una nota avverte che le date iniziali del range potrebbero non essere valide in
un prossimo futuro. Chiaro che, essendo il Calendario Gregoriano introdotto nel 1582
da Papa Gregorio XII, il precedente Calendario Giuliano aveva una composizione
diversa e come tutte le date precedenti il 1582 queste sono da considerare solo
date approssimate. DATETIME resta comunque uguale alla versione precedente.


TIMESTAMP
Vediamo qui il primo dei cambiamenti sostanziali. Come gia' detto nella premessa
i cambiamenti sono avvenuti man mano con l'evolversi della release 4.1, chi fosse
interessato ai vari step, puo' riferirsi in dettaglio al manuale.

http://dev.mysql.com/doc/mysql/en/TIMESTAMP_4.1.html

Il campo TIMESTAMP ha ora una dimensione fissa ed e' NULL. La visualizzazione del
valore e' identica al formato DATETIME YYYY-MM-DD HH:MM:SS. Non si potra' piu'
quindi scegliere il numero dei digit da visualizzare. A differenza della versione 4.0
dove il campo TIMESTAMP ad essere automaticamente aggiornato (INSERT o UPDATE) era
obbligatoriamente il primo (nel caso di piu' campi TIMESTAMP), ora e' possibile
scegliere quale campo dichiarare auto-aggiornante. La scelta puo' essere fatta al
momento della creazione della tabella (CREATE TABLE) oppure successivamente con
ALTER TABLE, tenendo pero' presente che di campi auto-aggiornanti puo' essercene
uno solo e quindi volendolo cambiare, si deve prima togliere la caratteristica
di CURRENT_TIMESTAMP all'altro campo se presente.

Esempi equivalenti dal manuale:

CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP DEFAULT CURRENT_TIMESTAMP
                  ON UPDATE CURRENT_TIMESTAMP);
CREATE TABLE t (
    ts1 TIMESTAMP DEFAULT 0,
    ts2 TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
                  DEFAULT CURRENT_TIMESTAMP);

Mentre per modificare con ALTER TABLE ho verificato:

ALTER TABLE t CHANGE ts2 ts2 TIMESTAMP
                DEFAULT 0;

ALTER TABLE t CHANGE ts1 ts1 TIMESTAMP
                DEFAULT CURRENT_TIMESTAMP
                ON UPDATE CURRENT_TIMESTAMP;

In questo modo si scambia il campo auto-aggiornante tra ts2 e ts1.


Altra cosa da tenere in considerazione e' il TIME-ZONE.
A partire dalla versione 4.1.3 e' possibile assegnare al server MySQL un TIME-ZONE
diverso da quello di sistema, per ogni client che si connette al database.
(Variabili "system time zone" e "time zone").

In merito precisiamo che:
CURRENT_DATE(), CURRENT_TIME(), CURRENT_TIMESTAMP(), FROM_UNIXTIME(), LOCALTIME(),
LOCALTIMESTAMP(), NOW(), SYSDATE() e UNIX_TIMESTAMP() rendono il valore del corrente
TIME-ZONE del client, mentre UTC_DATE(), UTC_TIME(), UTC_TIMESTAMP() rendono il
valore UTC. (UTC Universal Time Coordinate = GMT Greenwich Mean Time).

Quindi se nella tabella descritta in precedenza, assegnassimo al campo con DEFAULT 0
il valore estratto con UTC_TIMESTAMP troveremo l'ora UTC, mentre nell'altro auto-
aggiornante che ha CURRENT_TIMESTAMP() come default, troveremo l'ora locale (CET o CEST).

La variazione del TIME-ZONE e' piuttosto consistente per MySQL e sarebbe bene
trattarla a parte in modo piu' esaustivo. Anche perche' e' possibile introdurre
malfunzionamenti data/ora che potrebbero propagarsi anche ad altri applicativi.

rif.: http://dev.mysql.com/doc/mysql/en/Ti...e_support.html


FUNZIONI VARIATE.

Anche qui una dovuta premessa: Molti dei cambiamenti sono dovuti per avvicinarsi
alla compatibilita' con MaxDB. Per esempio l'introduzione dei microsecondi,
validi come sintassi, non gestiti nei campi del db ma solo da alcune funzioni...
Non riceverete un errore, ma solo un risultato zero. Il formato dei microsecondi
va da 000000 (sei zero) a 999999 (sei nove).

Gli esempi che seguono sono tratti da un articolo di Trudy Pelzer: Temporal
Functionality http://dev.mysql.com/tech-resources/.../4.1/time.html e dal
manuale: http://dev.mysql.com/doc/mysql/en/Da...functions.html

Tutti testati sulla suite predetta. Diamo ora un'occhiata alle poche funzioni variate.


ADDDATE - SUBDATE

Accettano ora un secondo e comodo parametro "numero_dei_giorni". Sara' quindi possibile
sommare o sottrarre rapidamente dei giorni da una data, che puo' essere esplicita,
da funzione, campo o variabile.

mysql> select adddate('2004-10-01',15), subdate('2004-10-01',15);
+--------------------------+--------------------------+
| adddate('2005-01-01',15) | subdate('2005-01-01',15) |
+--------------------------+--------------------------+
|2005-01-16                | 2004-12-17               |
+--------------------------+--------------------------+
1 row in set (0.20 sec)

Altro esempio: trovare i record con max 30 giorni di presenza:

SELECT * FROM table WHERE data BETWEEN SUBDATE(NOW(), 30) AND NOW()

Queste due funzioni si differenziano percio' dalle loro, in precedenza equivalenti,
DATE_ADD e DATE_SUB per la possibilita' di inserire un valore "giorno" senza ulteriori
istruzioni.


DATE_ADD - DATE_SUB
Continuano come in precedenza ed insieme alle due funzioni precedenti accettano ora
la costante dei microsecondi. I nuovi parametri sono: DAY_MICROSECOND,
HOUR_MICROSECOND, MINUTE_MICROSECOND, SECOND_MICROSECOND e MICROSECOND.

mysql> SELECT DATE_ADD('1992-12-31 23:59:59.000002',
                        INTERVAL '1.999999' SECOND_MICROSECOND) as msec;
+-----------------------------------+
| msec                              |
+-----------------------------------+
| 1993-01-01 00:00:01.000001        |
+-----------------------------------+

EXTRACT(intervallo_costante FROM espressione)
Funzione poco utilizzata ma utile per estrarre valori da una data/ora.

mysql> SELECT EXTRACT(YEAR_MONTH FROM NOW()) as anno_mese;
+------------+
| anno_mese  |
+------------+
|     200501 |
+------------+

Utilizza tutte le possibili combinazioni di valori previste dalle costanti usate dalle
funzioni sopra descritte. (cfr. manuale)


DATE_FORMAT - TIME FORMAT
Anche queste funzioni ora hanno un nuovo valore nel formato stringa che producono:
'%f' che rende appunto i microsecondi.
Inutile provarci... rendera' sempre 000000. Pero' non vi dara' errore.

DATE_FORMAT ha finalmente il degno compare che fara' il suo esatto inverso.... cioe'
mentre DATE_FORMAT prende una data e rende una stringa a piacere, STR_TO_DATE prende
una stringa e rende una data. Fara' felice chi memorizza la data nel campo VARCHAR
nel formato gg/mm/aaaa. Ma lo vedremo piu' avanti trattando le numerose nuove funzioni.


Fine parte 1

[piero.mac]

[Pillola] DATE/TIME e MySQL 4.1.x

PARTE SECONDA

In questa seconda parte vedremo le nuove funzionalita' temporali di questo rilascio.
Ci sono veramente tante novita' che semplificheranno, e di molto, la gestione della
data/ora. Ma ricordiamoci che le seguenti query non sono compatibili con le versioni
di MySQL precedenti la 4.1.x

ADDTIME(espressione_temporale, tempo_da_addizionare)
SUBTIME(espressione_temporale, tempo_da_sottrarre)

Queste due funzioni provvedono ad addizionare / sottrarre porzioni di tempo da un
DATETIME, oppure da un altro valore TIME. Se si passano valori non congruenti la
risposta sara' NULL.

SELECT addtime('23:12:10','02:20:10') as somma, subtime('01:12:10','15:12:10') as diff;
=>  somma = 25:32:20    diff = -14:00:00

SELECT addtime('2005-01-12 23:12:10','02:20:10') as somma,
       subtime('2005-01-12 01:12:10','15:12:10') as diff
=> somma = 2005-01-13 01:32:20   diff = 2005-01-11 10:00:00

Quindi il primo argomento di ADDTIME accetta un valore DATETIME "yyyy-mm-dd hh:mm:ss"
oppure un valore TIME "hh:mm:ss", mentre il secondo argomento di ADDTIME accetta
solo un valore TIME "hh:mm:ss"

Se tentate di sommare/sottrarre DATA e TIME in questo modo: ('2005-01-12', '12:01:20')
non riceverete un NULL, ma un risultato errato e fantasioso.


DATEDIFF(espressione_temporale, espressione_temporale)

Strabella nuova funzione.... Dato due date (sic!) rende il numero dei giorni che
intercorrono. Anche qui valori non corretti daranno NULL come risultato.

$data_nascita = '1769-08-15';    // nascita di Napoleone

$query = mysql_query("select DATEDIFF(NOW(),'$data_nascita')");
$res = mysql_fetch_row($query);
echo "
RISULTATO -> $res[0]";

RISULTATO -> 86118   //  (al 28 maggio 2005).

E cosi sappiamo quanto giorni avrebbe Napoleone al 28/05/2005.

Notare l'uso delle apici. Per campi, funzioni e costanti non ci vogliono.


TIMEDIFF(espressione_temporale, espressione_temporale)

Cugina della precedente questa funzione rende la differenza in hh:mm:ss tra due orari.
Accetta valori anche incompleti, con la seguente logica: se si immette una sola
cifra questa viene considerata come "secondi", Se le cifre sono due (es.: 12:35)
vengono considerate come hh:mm. Attenzione quindi all'impostazione degli argomenti.
Vediamo un po' di esempi:

select TIMEDIFF('23:35:30', '10:10:10') RISULTATO -> 13:25:20
select TIMEDIFF('23:35:30', '10:10')    RISULTATO -> 13:25:30
select TIMEDIFF('23:35:30', '10')       RISULTATO -> 23:35:20
select TIMEDIFF('23:35', '10:10:10')    RISULTATO -> 13:24:50
select TIMEDIFF('23:35', '10:10')       RISULTATO -> 13:25:00
select TIMEDIFF('35', '10')             RISULTATO -> 00:00:25
select TIMEDIFF('30', '10:10:50')       RISULTATO -> -10:10:20

Un risultato inatteso e' invece l'utilizzo di questa funzione con un valore DATETIME
negli argomenti. Viene trasformata in ore minuti secondi la differenza tra due
espressioni temporali:

select TIMEDIFF('2004-12-31 23:59:59', '2004-01-01 00:00:00') RISULTATO -> 8783:59:59
ecco il totale numero delle ore:min:sec di un anno bisestile.

select TIMEDIFF('2005-12-31 23:59:59', '2005-01-01 00:00:00') RISULTATO -> 8759:59:59
ed ecco quelle di un anno non bisesto.

select TIMEDIFF(NOW(), '2005-01-01 00:00:00');    RISULTATO -> 3541:55:23
differenza tra NOW() e l'inizio anno.... in ore:mm:ss

Oppure utilizzare TIMEDIFF nella clausola WHERE per un DELETE o un UPDATE....
Supponiamo che nel campo dataora ci sia il TIMESTAMP di immissione del dato,
e che sia, per esempio, l'inizio di una sessione su DB.

DELETE tabella WHERE TIMEDIFF(now(), dataora) >= '24:00:00'
UPDATE tabella set stato = 'scaduto' WHERE TIMEDIFF(now(), dataora) >= '24:00:00'

In questi casi verranno cancellati o aggiornati i dati presenti da piu' di 24 ore.


DATE(espressione temporale)
TIME(espressione temporale)

Queste due nuove funzioni potranno creare confusione per via delle omonime funzioni
PHP che hanno tutt'altra resa di dato. Queste due di MySQL estraggono rispettivamente
le rispettive porzioni di data e orario da un DATETIME oppure NULL se l'argomento
passato non e' valido.

SELECT date(NOW()) as data , time(NOW()) as ora
->  data 2005-01-12  -  ora  14:17:22       // data ora corrente.

MAKEDATE(anno, giorno dell'anno)
MAKETIME(ora, minuti, secondi)

MAKEDATE rende la data che risulta dalla combinazione dell'anno e del numero del
giorno nell'anno. i valori devono essere degli INTERI. E' il naturale complemento
di DAYOFYEAR. Questa funzione rende NULL se il giorno dell'anno e' negativo oppure 0,
mentre se il numero e' superiore agli effettivi giorni dell'anno ... continua
nell'anno successivo.

SELECT MAKEDATE(2005, 250)         RISULTATO -> 2005-09-07
SELECT MAKEDATE(2005, 250 + 200)   RISULTATO -> 2006-03-26

MAKETIME vuole tre argomenti INTERI pure loro (senza apici). Non fa la somma, ma si
limita a controllare che i valori siano nel range ammesso dal formato campo TIME ed
assembla l'orario in formato hh:mm:ss
Anche se per le ore pare non abbia limiti (anche il frm TIME), i minuti e secondi
invece sono da 00 a 59

SELECT MAKETIME(123, 25 , 20)     RISULTATO -> 123:25:20

DAY

Sinonimo di DAYOFMONTH rende il numero del giorno di una data. Se la data e' MULLA
risolvera' l'argomento come NULL.


LAST_DAY

Questa funzione prende un data e rende la data corrispondente all'ultimo giorno del
mese considerato nella data passata come argomento. Se la data passata non e'
valida rendera' NULL.


MICROSECOND

Qui la funzione si limita ad estrapolare i microsecondi passati con una data ma,
come gia' detto, non ad estrarre i microsecondi da una funzione.

mysql> SELECT microsecond ('2005-05-23 10:12:30.912399');
+--------------------------------------------+
| microsecond ('2005-05-24 14:12:30.912399') |
+--------------------------------------------+
|                                     912399 |
+--------------------------------------------+
1 row in set (0.00 sec)

WEEKOFYEAR(date)

Calcola la settimana dell'anno in un range 1 - 53. Si unisce ad altre due funzioni
preesistenti: WEEK(date) - YEARWEEK(date) a cui rimandiamo per il confronto nella
"pillola" precedente. http://forum.html.it/forum/showthrea...hreadid=459634

La prima settimana dell'anno, per questa funzione, e' quella in cui cade il primo lunedi'
oppure con altra valutazione, la settimana completa che contiene il giorno 4 gennaio.

mysql> SELECT WEEKOFYEAR('2005-01-02'), WEEKOFYEAR('2005-01-03');
+--------------------------+--------------------------+
| WEEKOFYEAR('2005-01-02') | WEEKOFYEAR('2005-01-03') |
+--------------------------+--------------------------+
|                       53 |                        1 |
+--------------------------+--------------------------+
1 row in set (0.00 sec)

STR_TO_DATE

Bella nuova funzione. E l'opposto di DATE_FORMAT. Da una stringa abbiamo ora
la facile possibilita' di ricavare una data. Il formato ricevuto sara' quello
di mysql e cioe' yyyy-mm-dd hh:mm:ss. I due parametri da passare sono:
il primo la data in formato stringa. La seconda che indichi come sono posizionati
i valori all'interno della stringa. I valori ammessi sono gli stessi di quelli
ammessi per DATE_FORMAT a cui si aggiunge ora "%f" per i microsecondi.

mysql> select str_to_date( '24/05/05 14.10.30' , '%d/%m/%y %H.%i.%s');
+---------------------------------------------------------+
| str_to_date( '24/05/05 14.10.30' , '%d/%m/%y %H.%i.%s') |
+---------------------------------------------------------+
| 2005-05-24 14:10:30                                     |
+---------------------------------------------------------+
1 row in set (0.00 sec)

Come si puo' vedere nel primo parametro passiamo un valore che e' una stringa,
nel secondo la composizione della stringa.
Ancora un esempio....

mysql> select str_to_date( '05, 24-2005 14:10' , '%m, %d-%Y %i:%s');
+-------------------------------------------------------+
| str_to_date( '05, 24-2005 14:10' , '%m, %d-%Y %i:%s') |
+-------------------------------------------------------+
| 2005-05-24 00:14:10                                   |
+-------------------------------------------------------+
1 row in set (0.00 sec)

**************** ********************* ************************** *****************

Fermiamoci qui. Ma le novita' di MySQL 4.1.x e le funzioni temporali non sono terminate.

Ci sarebbero ancora i dettagli di TIMESTAMP, GET_FORMAT (i formati sono prelevati da MaxDb).
Tutta la serie UTC_* (UTC_DATE UTC_TIME, UTC_TIMESTAMP) che rendono il tempo UTC.
Poi ancora la gestione del TIME ZONE che e' complessa se non fosse altro che per
la carenza di documentazione, non ultimo il rischio di casino con altri applicativi
che utilizzano il TIME ZONE.

Ma rimandiamo a tempi piu' opportuni oppure a qualcuno di buona volonta' il loro
completamento. Alcuni link ...

http://dev.mysql.com/doc/mysql/en/datetime.html
http://dev.mysql.com/doc/mysql/en/da...culations.html
http://dev.mysql.com/doc/mysql/en/da...functions.html

Alla prossima...

[gm]

Aggiunta al thread in rilievo

[piero.mac]

Originariamente inviato da gm
Aggiunta al thread in rilievo

Thanx...


aggiungo un link per chi volesse cimentarsi con il TIME ZONE.

http://dev.mysql.com/doc/mysql/en/ti...e-support.html

Un pacchetto di descrizione delle tabelle timezone nel database MySQL.

http://dev.mysql.com/downloads/timezones.html

un occhiata alle problematiche varie.

http://dev.mysql.com/doc/mysql/en/re...-features.html

[l'evangelista]

select TIMEDIFF('23:35:30', '10:10:10') RISULTATO -> 13:25:20


select TIMEDIFF('2004-12-31 23:59:59', '2004-01-01 00:00:00') RISULTATO -> 8783:59:59

------------------------------------------------------------------
Il primo timediff mi funziona perfettamente, il secondo no
mi restituisce sempre 0.00.00

Quando parlavi di 'risultato inatteso' intendi che non va utilizzata con formati YYYY-MM-DD HH-MM-SS ?

[piero.mac]

Originariamente inviato da l'evangelista
select TIMEDIFF('23:35:30', '10:10:10') RISULTATO -> 13:25:20


select TIMEDIFF('2004-12-31 23:59:59', '2004-01-01 00:00:00') RISULTATO -> 8783:59:59

------------------------------------------------------------------
Il primo timediff mi funziona perfettamente, il secondo no
mi restituisce sempre 0.00.00

Quando parlavi di 'risultato inatteso' intendi che non va utilizzata con formati YYYY-MM-DD HH-MM-SS ?

l'ho riprovato ora e sul mio pc funziona.

mysql> select TIMEDIFF('2004-12-31 23:59:59', '2004-01-01 00:00:00');
+--------------------------------------------------------+
| TIMEDIFF('2004-12-31 23:59:59', '2004-01-01 00:00:00') |
+--------------------------------------------------------+
| 8783:59:59                                             |
+--------------------------------------------------------+
1 row in set (0.00 sec)

Un risultato inatteso nel senso che passando una data rende solo delle ore:min:sec. Inatteso anche perche' il range delle ore nel formato TIME e' '-838:59:59' up to '838:59:59'. Quando il risultato supera il range offerto dubito sempre della sua attendibilita'. Puo' andare si, o andare no. Dipende da tante cose. Se da te non va, sei un caso no. Verifica bene.

[l'evangelista]

mi correggo, da riga di comando funziona.
è da mysqlfront che restituisce il dato corretto se utilizzo solo i parametri ora/minuti/secondi, e 0.00.00 se utilizzo anche anno/giorno/mese.

a questo punto dovrebbe essere un bug di mysqlfront.. spero, visto che mi serve per un gestionale.
devo trovare tutti i valori inferiori o uguali ad una data (confrontando anche ore/minuti/secondi), pensavo di usare questa funzione ed estrarre tutti i record inferiori di almeno 1 secondo alla data di confronto

[piero.mac]

per funzionare funziona. Anzi, credo gestisca anche i microsecondi a livello di calcolo. Non come funzioni pero', i microsecondi (e altre funzioni temporali), sono stati aggiunti solo per avvicinarsi alla compatibilita' SQL con MAXdb. Se usi mysqlfront 2.5 occhio che ci sono pure altre cose che non funzionano. La ver. 2.5 e' ferma al mysql 4.0 delle prime versioni.

[l'evangelista]

Originariamente inviato da piero.mac
per funzionare funziona. Anzi, credo gestisca anche i microsecondi a livello di calcolo. Non come funzioni pero', i microsecondi (e altre funzioni temporali), sono stati aggiunti solo per avvicinarsi alla compatibilita' SQL con MAXdb. Se usi mysqlfront 2.5 occhio che ci sono pure altre cose che non funzionano. La ver. 2.5 e' ferma al mysql 4.0 delle prime versioni.

ok grazie della info

[laryusz]

Ciao. Ho un problema che dovrebbe essere piuttosto banale, ma non riesco a risolverlo...

Ho una chat, i messaggi inviati dagli utenti vengono inseriti in una tabella mysql, dalla quale vengono selezionati per essere visualizzati. Ogni messaggio viene cancellato dopo un'ora dal suo invio, secondo un codice (che spero sia esatto) del tipo:

mysql_query("DELETE FROM chat WHERE orario+10000 < now()");
(il campo orario è datetime)

Sempre allo scoccare della mezzanotte, e solo a quell'ora, vengono cancellati tutti i messaggi (anche quelli che sono stati inviati da meno di un'ora). Dov'è l'errore?
Grazie