MySql e UTC date

February 2nd 2011 by Paolo Bindi in: MySql | Tags: MySql

Per convertire una data ad un qualsiasi fuso orario, MySql mette a disposizione la funzione CONVERT_TZ (link al manuale ufficiale)

Prima di utilizzarla pero' c'e' bisogno di creare, se non esistono, le seguenti tabelle nella base dati di sistema "mysql".
Ovvero

mysql.time_zone
mysql.time_zone_leap_second
mysql.time_zone_name
mysql.time_zone_transition
mysql.time_zone_transition_type

Per creare questa configurazione, e' necessario accedere come amministratore, e digitare da linea di comando

$ mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -YourRootName -p mysql

Non mi soffermo sul contenuto di queste tabelle, di cui mi riservo un articolo apposito, ma vi illustro direttamente il codice che utilizzo per la conversione. (Se nel frattempo vi interessa approfondire le informazioni contenute in tali tabelle, questo e' link al manuale ufficiale).

Per illustrare la soluzione che ho adottato, e' necessario fare qualche premessa.
1. La data che uso e' in valore assoluto. Ovvero separata dal valore del fuso orario
2. Il fuso orario deve essere descrittivo, ovvero 'Europe/London' per 'GMT', 'Etc/GMT+4' per 'GMT+4', 'Europe/Berlin' per 'CEMT', 'Africa/Algiers' per CET, 'America/Boise' per 'PDT'.
Per comodita', visto che sara' l'utente a scegliere il fuso orario con un combo, questa configurazione l'ho salvata in una tabella, di cui questo e' il contenuto. Ricordo che utilizzero' il campo Description e non Abbreviation.
Nota: Si potrebbero utilizzare direttamente le tabelle mysql.xxxx, ma sara' argomento di un altro articolo.

id,Abbreviation, Description
1, 'UTC', 'Etc/UTC'
2, 'GMT', 'Europe/London'
3, 'GMT+1', 'Etc/GMT+1'
4, 'GMT+2', 'Etc/GMT+2'
5, 'GMT+3', 'Etc/GMT+3'
6, 'GMT+4', 'Etc/GMT+4'
7, 'GMT+5', 'Etc/GMT+5'
8, 'GMT+6', 'Etc/GMT+6'
9, 'GMT+7', 'Etc/GMT+7'
10, 'GMT+8', 'Etc/GMT+8'
11, 'GMT+9', 'Etc/GMT+9'
12, 'GMT+10', 'Etc/GMT+10'
13, 'GMT+11', 'Etc/GMT+11'
14, 'GMT+12', 'Etc/GMT+12'
15, 'GMT-1', 'Etc/GMT-1'
16, 'GMT-2', 'Etc/GMT-2'
17, 'GMT-3', 'Etc/GMT-3'
18, 'GMT-4', 'Etc/GMT-4'
19, 'GMT-5', 'Etc/GMT-5'
20, 'GMT-6', 'Etc/GMT-6'
21, 'GMT-7', 'Etc/GMT-7'
22, 'GMT-8', 'Etc/GMT-8'
23, 'GMT-9', 'Etc/GMT-9'
24, 'GMT-10', 'Etc/GMT-10'
25, 'GMT-11', 'Etc/GMT-11'
26, 'GMT-12', 'Etc/GMT-12'
27, 'CEMT', 'Europe/Berlin'
28, 'CET', 'Africa/Algiers'
29, 'PDT', 'America/Boise'
30, 'CEST', 'Africa/Algiers'

3. Il valore dell'UTC locale, a cui convertire la data, per le mie esigenze e' 'Europe/Madrid'. Ma chiaramente potreste pensare di rendelo totalmente parametrico, indicando anche questo parametro come variabile, utilizzando sempre il valore del campo "description" indicato nel punto 2.

Dopo questa lunga (ma necessaria premessa, che spero vi faccia "perdere" meno tempo che a me), questa e' finalmente la select che mi permette di convertire tutte le date (ed il relativo fuso orario) alla ora di Madrid/Roma/Parigi/Berlino (ovvero GMT+1)

SELECT a.date_conection,
       b.abbreviation as time_zone,
       CONVERT_TZ(a.date_conection,b.description,'Europe/Madrid') as date_conection_utc
  FROM table_all_date a,
       table_time_zone b
 WHERE a.time_zone_id=b.id
   AND ----

Chiaramente la tabella all_date sara' la vostra tabella in cui avrete inserito le date ancora da convertire ed 'Europe/Madrid', il fuso orario a cui volete convertire.

Nel caso che riscontrate qualche errore o se volete contattarmi per qualche consiglio o suggerimento, utilizzate la form della sezione contatti