Funzione DATEDIFF(), TIMEDIFF() e TIMESTAMPDIFF()
|
Gli argomenti di questa pagina
- La funzione DATEDIFF()
- La funzione TIMESTAMPDIFF() per calcolare gli anni
- La funzione TIMESTAMPDIFF() per calcolare gli anni, i mesi o i giorni
- La funzione TIMEDIFF()
- Calcoli su orari a cavallo della mezzanotte con TIMEDIFF()
|
Definizione e sintassi della funzione DATEDIFF() e TIMEDIFF().
La funzione DATEDIFF() restituisce il tempo trascorso tra due date.
La funzione TIMEDIFF() restituisce le differenze tra due espressioni di tempo o datetime. Le due espressioni devono essere dello stesso tipo (DATE,
DATETIME,
TIMESTAMP,
TIME).
Questa la sintassi
DATEDIFF(endDate, startDate)
TIMEDIFF( endDate , startDate )
La funzione DATEDIFF()
Calcolare la differenza di tempo trascorso tra la data letta nel DB e la data corrente restituita in giorni trascorsi.
esempio 1
1
2
3
4
5
6
7
|
<?php
$Query = "SELECT cognome, nome,
DATE_FORMAT( data_nascita, '%d-%m-%Y' ) AS nato,
DATEDIFF(NOW(),data_nascita) AS DiffDate
FROM sql_rubrica
";
?>
|
|
101 record trovati
15 record visualizzati
| cognome |
nome |
nato |
DiffDate |
| Migliavacca |
Luigi |
22-01-2004 |
6738 |
| Rizzi |
Carlo |
22-11-2007 |
5338 |
| Liberali |
Franca |
20-02-2004 |
6709 |
| Di Maggio |
Mario |
21-05-2009 |
4792 |
| Scicchitano |
Andrea |
12-01-1997 |
9304 |
| Edelvisi |
Maurizio |
02-07-1987 |
12786 |
| Gerardini |
Silvano |
12-08-2009 |
4709 |
| Pasotti |
Alessio |
14-11-1987 |
12651 |
| Calvi |
Giovanni |
26-07-1987 |
12762 |
| Poggi |
Giuseppe |
25-01-1987 |
12944 |
| Curti |
Oreste |
26-06-1988 |
12426 |
| Borsotti |
Mirella |
19-07-1987 |
12769 |
| Franchinotti |
Anna |
10-05-2009 |
4803 |
| Pischedda |
Giovanni |
08-08-1999 |
8366 |
| Maini |
Marilena |
20-03-2002 |
7411 |
| ... |
... |
... |
... |
|
In questo secondo esempio viene calcolata la differenza di tempo trascorso tra tra due date vicine tra loro (si tratta di orari lavorativi, quindi l'intervallo è brevissimo).
In questo caso la massima differenza è '1' e solo se gli orari sono a cavallo della mezzanotte.
Calcola la differenza di tempo trascorso tra due date lette nel DB
esempio 2
1
2
3
4
5
6
7
8
|
<?php
$Query = "SELECT nome,
DATE_FORMAT( data_start, '%d-%m-%Y %k:%i:%s' ) AS start,
DATE_FORMAT( data_end, '%d-%m-%Y %k:%i:%s' ) AS end,
DATEDIFF( data_end , data_start ) AS DiffDate
FROM sql_ore_lavorative
";
?>
|
|
8 record trovati
8 record visualizzati
| nome |
start |
end |
DiffDate |
| Migliavacca Luigi |
08-09-2011 10:00:00 |
08-09-2011 15:00:00 |
0 |
| Rizzi Carlo |
08-09-2011 8:00:00 |
08-09-2011 14:00:00 |
0 |
| Liberali Franca |
07-09-2011 22:40:00 |
08-09-2011 8:00:00 |
1 |
| Di Maggio Mario |
08-09-2011 13:59:00 |
08-09-2011 22:30:00 |
0 |
| Scicchitano Andrea |
13-10-2013 7:05:00 |
13-10-2013 14:15:00 |
0 |
| Edelvisi Maurizio |
13-10-2013 13:58:00 |
13-10-2013 21:05:00 |
0 |
| Gerardini Silvano |
13-10-2013 20:58:00 |
14-10-2013 7:05:00 |
1 |
| Pasotti Alessio |
14-10-2013 6:43:00 |
14-10-2013 14:11:00 |
0 |
|
La funzione TIMESTAMPDIFF() per calcolare gli anni
Per avere la differenza di tempo trascorso tra due date in anni occorre rivolgersi alla funzione TIMESTAMPDIFF() che accetta un terzo argomento che indica quale unità di misura deve essere usata nel mostrare il risultato restituito.
Mostra gli anni dei miei contatti
esempio 3
1
2
3
4
5
6
7
8
|
<?php
$Query = "SELECT cognome, nome,
DATE_FORMAT( data_nascita, '%d-%m-%Y' ) AS nato,
TIMESTAMPDIFF(YEAR, data_nascita, CURDATE( )) AS eta
FROM sql_agenda
ORDER BY eta DESC
";
?>
|
|
103 record trovati
15 record visualizzati
| cognome |
nome |
nato |
eta |
| pinco |
pallino |
01-01-1934 |
88 |
| Uberti |
Federico |
12-01-1947 |
75 |
| Forlini |
Andrea |
24-12-1948 |
73 |
| giulio |
cesare |
10-04-1950 |
72 |
| Vitulano |
Vincenzo |
15-02-1951 |
71 |
| Deiana |
Alberino |
05-05-1951 |
71 |
| Biancarosa |
Francesca |
05-09-1956 |
65 |
| Zuna |
Clotilde |
15-08-1961 |
60 |
| Costanza |
Antonietta |
11-11-1966 |
55 |
| Daniel |
Nicola |
21-02-1977 |
45 |
| Edelvisi |
Maurizio |
02-07-1987 |
35 |
| Querio |
Angela |
30-04-1987 |
35 |
| Poggi |
Giuseppe |
25-01-1987 |
35 |
| Ravizzola |
Stefano |
16-04-1988 |
34 |
| Di Bartolomeo |
Onorino |
02-07-1988 |
34 |
| ... |
... |
... |
... |
|
La funzione TIMESTAMPDIFF() per calcolare gli anni, i mesi o i giorni
Nel prossimo esempio viene mostrato come calcolare la differenza in anni, mesi o giorni. Da non confondere col calcolo degli anni mesi è giorni di cui si parlerà in "Età - Compleanni".
Mostra gli anni, i medsi o i giorni dei miei contatti
esempio 3
1
2
3
4
5
6
7
8
9
10
|
<?php
$Query = "SELECT cognome, nome,
DATE_FORMAT( data_nascita, '%d-%m-%Y' ) AS nato,
TIMESTAMPDIFF(YEAR, data_nascita, CURDATE( )) AS anni,
TIMESTAMPDIFF(MONTH, data_nascita, CURDATE( )) AS mesi,
TIMESTAMPDIFF(DAY, data_nascita, CURDATE( )) AS giorni
FROM sql_agenda
ORDER BY anni DESC
";
?>
|
|
103 record trovati
15 record visualizzati
| cognome |
nome |
nato |
anni |
mesi |
giorni |
| pinco |
pallino |
01-01-1934 |
88 |
1062 |
32326 |
| Uberti |
Federico |
12-01-1947 |
75 |
905 |
27567 |
| Forlini |
Andrea |
24-12-1948 |
73 |
882 |
26855 |
| giulio |
cesare |
10-04-1950 |
72 |
866 |
26383 |
| Vitulano |
Vincenzo |
15-02-1951 |
71 |
856 |
26072 |
| Deiana |
Alberino |
05-05-1951 |
71 |
853 |
25993 |
| Biancarosa |
Francesca |
05-09-1956 |
65 |
789 |
24043 |
| Zuna |
Clotilde |
15-08-1961 |
60 |
730 |
22238 |
| Costanza |
Antonietta |
11-11-1966 |
55 |
667 |
20324 |
| Daniel |
Nicola |
21-02-1977 |
45 |
544 |
16569 |
| Edelvisi |
Maurizio |
02-07-1987 |
35 |
420 |
12786 |
| Querio |
Angela |
30-04-1987 |
35 |
422 |
12849 |
| Poggi |
Giuseppe |
25-01-1987 |
35 |
425 |
12944 |
| Ravizzola |
Stefano |
16-04-1988 |
34 |
410 |
12497 |
| Di Bartolomeo |
Onorino |
02-07-1988 |
34 |
408 |
12420 |
| ... |
... |
... |
... |
... |
... |
|
La funzione TIMEDIFF()
In questo esempio viene utilizzata l'altra funzione simile alla funzione DATEDIFF() ed è la funzione TIMEDIFF() che, invece di restituire la differenza in giorni la restituisce in formato orario (hh:mm:ss). Per gli orari a cavallo della mezzanotte non ci sono problemi se il formato del campo è DATE o
DATETIME.
Mostra il tempo trascorso tra due date in formato DATE-TIME comprese quelle a cavallo della mezzanotte
esempio 4
1
2
3
4
5
6
7
8
|
<?php
$Query = "SELECT nome,
DATE_FORMAT( data_start, '%d-%m-%Y %k:%i:%s' ) AS start,
DATE_FORMAT( data_end, '%d-%m-%Y %k:%i:%s' ) AS end,
TIMEDIFF( data_end , data_start ) AS DiffDate
FROM sql_ore_lavorative
";
?>
|
|
8 record trovati
8 record visualizzati
| nome |
start |
end |
DiffDate |
| Migliavacca Luigi |
08-09-2011 10:00:00 |
08-09-2011 15:00:00 |
05:00:00 |
| Rizzi Carlo |
08-09-2011 8:00:00 |
08-09-2011 14:00:00 |
06:00:00 |
| Liberali Franca |
07-09-2011 22:40:00 |
08-09-2011 8:00:00 |
09:20:00 |
| Di Maggio Mario |
08-09-2011 13:59:00 |
08-09-2011 22:30:00 |
08:31:00 |
| Scicchitano Andrea |
13-10-2013 7:05:00 |
13-10-2013 14:15:00 |
07:10:00 |
| Edelvisi Maurizio |
13-10-2013 13:58:00 |
13-10-2013 21:05:00 |
07:07:00 |
| Gerardini Silvano |
13-10-2013 20:58:00 |
14-10-2013 7:05:00 |
10:07:00 |
| Pasotti Alessio |
14-10-2013 6:43:00 |
14-10-2013 14:11:00 |
07:28:00 |
|
E' ovvio che usando la funzione TIMEDIFF() su valori di tipo DATE il risultato che si ottiene va da 00:00:00 se le date sono uguali o con incremento di 24 ore per date superiori come 24:00:00, ecc.
Mostra il tempo trascorso tra due date che hanno il solo formato DATE
esempio 5
1
2
3
4
5
6
7
8
|
<?php
$Query = "SELECT nome,
DATE_FORMAT( data_entra, '%d-%m-%Y' ) AS start,
DATE_FORMAT( data_esce, '%d-%m-%Y' ) AS end,
TIMEDIFF( data_esce, data_entra ) AS DiffDate
FROM sql_ore_lavorative
";
?>
|
|
8 record trovati
8 record visualizzati
| nome |
start |
end |
DiffDate |
| Migliavacca Luigi |
08-09-2011 |
08-09-2011 |
00:00:00 |
| Rizzi Carlo |
08-09-2011 |
08-09-2011 |
00:00:00 |
| Liberali Franca |
07-09-2011 |
08-09-2011 |
24:00:00 |
| Di Maggio Mario |
08-09-2011 |
08-09-2011 |
00:00:00 |
| Scicchitano Andrea |
13-10-2013 |
13-10-2013 |
00:00:00 |
| Edelvisi Maurizio |
13-10-2013 |
13-10-2013 |
00:00:00 |
| Gerardini Silvano |
13-10-2013 |
14-10-2013 |
24:00:00 |
| Pasotti Alessio |
14-10-2013 |
14-10-2013 |
00:00:00 |
|
Calcoli su orari a cavallo della mezzanotte con TIMEDIFF()
Se nell'esempio precedente (esempio 4) Il calcolo ha funzionato, in questo esempio sembra non funzionare.
Questo è dovuto al fatto che, mentre nel precedente esempio si eseguiva il calcolo su un campo composto da data - orario, nel seguente viene eseguito sul semplice orario, senza data.
Nel prossimo esempio si esegue la funzione TIMEDIFF() su orari anzichè su date-ore. In questo caso, se l'orario è nell'arco della giornata il calcolo non da problemi, ma se sta a cavallo della mezzanotte cominciamo ad avere dei problemi, come si può osservare nella colonna "DiffDate".
Per risolvere il problema chiediamo aiuto ad altre due funzioni: la funzione TIME_TO_SEC() e la funzione SEC_TO_TIME() che vedremo più sotto e nella prossima pagina.
Mostra il tempo trascorso tra due orari, compresi quelli a cavallo della mezzanotte
esempio 6
1
2
3
4
5
6
|
<?php
$Query = "SELECT nome, entra, esce,
TIMEDIFF( esce, entra ) AS DiffDate
FROM sql_ore_lavorative
";
?>
|
|
8 record trovati
8 record visualizzati
| nome |
entra |
esce |
DiffDate |
| Migliavacca Luigi |
10:00:00 |
15:00:00 |
05:00:00 |
| Rizzi Carlo |
08:00:00 |
14:00:00 |
06:00:00 |
| Liberali Franca |
22:40:00 |
08:00:00 |
-14:40:00 |
| Di Maggio Mario |
13:59:00 |
22:30:00 |
08:31:00 |
| Scicchitano Andrea |
07:05:00 |
14:15:00 |
07:10:00 |
| Edelvisi Maurizio |
13:58:00 |
21:05:00 |
07:07:00 |
| Gerardini Silvano |
20:58:00 |
07:05:00 |
-13:53:00 |
| Pasotti Alessio |
06:43:00 |
14:11:00 |
07:28:00 |
|
esempio 6 finale
1
2
3
4
5
6
7
8
|
<?php
$Query = "SELECT nome, entra, esce,
if(entra<=esce,
SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(esce,entra))),
SEC_TO_TIME(TIME_TO_SEC(TIMEDIFF(esce,entra))+86400)) AS orario
FROM sql_ore_lavorative
";
?>
|
|
8 record trovati
8 record visualizzati
| nome |
entra |
esce |
orario |
| Migliavacca Luigi |
10:00:00 |
15:00:00 |
05:00:00 |
| Rizzi Carlo |
08:00:00 |
14:00:00 |
06:00:00 |
| Liberali Franca |
22:40:00 |
08:00:00 |
09:20:00 |
| Di Maggio Mario |
13:59:00 |
22:30:00 |
08:31:00 |
| Scicchitano Andrea |
07:05:00 |
14:15:00 |
07:10:00 |
| Edelvisi Maurizio |
13:58:00 |
21:05:00 |
07:07:00 |
| Gerardini Silvano |
20:58:00 |
07:05:00 |
10:07:00 |
| Pasotti Alessio |
06:43:00 |
14:11:00 |
07:28:00 |
|
