Ebbene si, dopo un più di un mese di astineza proprio il 13 Agosto torno a scrivere… il caldo inizia a farmi male!
(ndr tranquilli dopo ferragosto parto pure io, tra poco mi mimetizzo con le pareti bianche).

Tornando a noi, ogni tanto trovo qualche funzionalità carina del DB “open source” più famoso al mondo ma quando mi serve devo sempre chiedere a google (che guarda caso ha più memoria di me).
Ho deciso quindi di raggruppare in questo articolo i metodi per lavorare con le date direttamente a livello di database e non a quello applicativo… in poche parole basta perdere tempo con oggetti SimpleDateFormatter, Date, Formatter e per i meno smanettoni taglia e incolla di stringhe.

Ipotizziamo di avere a disposizione una tabella con il campo “data” di tipo datetime.

1) Estrarre i record la cui “data” è inferiore a 30 giorni rispetto alla data odierna:

select * from tbl where sysdate() >= ADDDATE(data,30)

2) Formattare la “data” dal formato yyyy-MM-dd (presente sul db) in quello italiano dd/MM/yyyy:

select DATE_FORMAT(data,’%d/%m/%Y’) as ‘data formattata’ from tbl
utilizzando %M al posto di %m otterremo il mese in formato testo in inglese
utilizzando %y al posto di %Y otterremo l’anno sotto forma di 2 cifre
Per una lista completa dei “simboli” da utilizzare con il metodo DATE_FORMAT cliccate qui.

3) Formattare la “data” dal formato yyyy-MM-dd (presente sul db) in quello italiano dd/MM/yyyy personalizzando l’output:

select DATE_FORMAT(data,’Il giorno %d del mese %M anno %Y stavo dormendo’) as ‘data formattata’ from tbl

4) Convertire in unix timestamp un campo datetime:

select UNIX_TIMESTAMP(data) from tbl

5) Convertire in data un campo unix timestamp

select FROM_UNIXTIME(valore_unixtimestamp) from tbl

6) Estrarre i record inseriti meno di una settimana fa, utilizzando DATE_SUB e INTERVAL

select * from tbl where data>= DATE_SUB( CURDATE( ) , INTERVAL 7 DAY )

7) Estrarre il nome di un giorno (sotto forma di testo) di una qualsiasi data:

select DAYNAME(data) from tbl
esempio 13 Agosto 2007 = “Monday”

8 ) Estrarre il numero di un giorno (1=lunedi, 2=martedi…) di una qualsiasi data:

select DAYOFMONTH(data) from tbl

9) Estrarre il nome di un mese(sotto forma di testo) di una qualsiasi data:

select MONTHNAME(data) from tbl
esempio 13 08 2007 = “August”

10) Estrarre il numero di un mese (1=gennaio, 2=febbraio…) di una qualsiasi data:

select MONTH(data) from tbl
utilizzando YEAR si otterrà il numero dell’anno.

11) Numero di giorni passati da una determinata data:

select (TO_DAYS(NOW()) - TO_DAYS(data)) from tbl

12) Sottrarre N giorni ad una determinata data (esempio 31):

select SUBDATE(data_registrazione, 31) from tbl

13) Sottrarre un mese da una data senza dover pensare al numero di giorni:

select SUBDATE(data_registrazione, INTERVAL 1 MONTH) from tbl

14) Differenza tra 2 date:

select PERIOD_DIFF(data1, data2) from tbl

15) Aggiungere un ora e mezza (1:30) ad un campo datetime:

select DATE_ADD(data, INTERVAL ‘1:30′ HOUR_MINUTE) as somma from tbl

16) Differenza tra due date sottoforma di ore minuti e secondi:

select TIMEDIFF(data1,dat) as diff from tbl
select TIMEDIFF(’2007-08-10 11:14:15′, ‘2007-08-13 11:14:15′) as diff from tbl

17) Convertire una data da un timezone ad un altro:

select CONVERT_TZ(data,’GMT’,'MET’)
es. MET : Europe/Moscow
NOTA : Per utilizzare questa funzione passando come parametri il codice timezone è necessario avere la tabella mysq.time_zone_name popolata (vedi qui)
In caso contrario è possibile passare come parametro la differenza di ore tra i due timestamp, ad esempio:
select CONVERT_TZ(data,’+00:00′,’+10:00′)