MySQL dovrebbe avere il proprio fuso orario impostato su UTC?

Domanda successiva di https://serverfault.com/questions/191331/should-servers-have-theirtimezone-set-to-gmt-utc

Il fuso orario MySQL dovrebbe essere impostato su UTC o dovrebbe essere impostato allo stesso fuso orario del server o del PHP? (Se non è UTC)

Quali sono i pro e i contro?

Sembra che non importa quale fuso orario è presente sul server finché si ha il tempo impostato per il fuso orario corrente, si conosce il fuso orario delle colonne datetime che si memorizzano e si è consapevoli dei problemi con l’ora legale.

D’altra parte, se hai il controllo dei fusi orari dei server con cui lavori, puoi avere tutto impostato su UTC internamente e non preoccuparti mai dei fusi orari e dell’ora legale.

Ecco alcune note che ho raccolto su come lavorare con i fusi orari come una forma di cheatsheet per me e per gli altri che potrebbe influenzare il fuso orario scelto dalla persona per il suo server e come memorizzerà la data e l’ora.

Cheatsheet MySQL Timezone

Gli appunti:

  1. La modifica del fuso orario non cambierà il datetime o timestamp , ma selezionerà un diverso datetime dalle colonne timestamp
  2. UTC non utilizza l’ora legale, GMT (la regione), GMT (il fuso orario) non lo fa (GMT confonde anche la definizione di secondi, motivo per cui è stato inventato UTC).
  3. Avvertimento! UTC ha secondi bisestili, che assomigliano a ‘2012-06-30 23:59:60’ e possono essere aggiunti a caso, con 6 mesi di preavviso, a causa del rallentamento della rotazione delle terre
  4. Avvertimento! i diversi fusi orari regionali potrebbero produrre lo stesso valore datetime a causa dell’ora legale
  5. La colonna timestamp supporta solo date 1970-01-01 00:00:01 a 2038-01-19 03:14:07 UTC, a causa di una limitazione .
  6. Internamente una colonna timestamp MySQL viene memorizzata come UTC ma quando si seleziona una data MySQL la convertirà automaticamente nel fuso orario della sessione corrente.

    Quando si memorizza una data in un timestamp, MySQL assumerà che la data sia nel fuso orario della sessione corrente e la converta in UTC per la memorizzazione.

  7. MySQL può memorizzare date parziali in colonne datetime, che assomigliano a “2013-00-00 04:00:00”
  8. MySQL memorizza “0000-00-00 00:00:00” se si imposta una colonna datetime come NULL, a meno che non si imposti specificatamente la colonna per consentire il null quando viene creata.
  9. Leggi questo

Per selezionare una colonna timestamp in formato UTC

indipendentemente dal fuso orario in cui si trova la sessione corrente di MySQL:

SELECT CONVERT_TZ(`timestamp_field`, @@session.time_zone, '+00:00') AS `utc_datetime` FROM `table_name` 

Puoi anche impostare il sever o il fuso orario della sessione globale o corrente su UTC e quindi selezionare il timestamp in questo modo:

 SELECT `timestamp_field` FROM `table_name` 

Per selezionare il datetime corrente in UTC:

 SELECT UTC_TIMESTAMP(); SELECT UTC_TIMESTAMP; SELECT CONVERT_TZ(NOW(), @@session.time_zone, '+00:00'); 

Esempio di risultato: 2015-03-24 17:02:41

Per selezionare il datetime corrente nel fuso orario della sessione

 SELECT NOW(); SELECT CURRENT_TIMESTAMP; SELECT CURRENT_TIMESTAMP(); 

Per selezionare il fuso orario che è stato impostato all’avvio del server

 SELECT @@system_time_zone; 

Restituisce “MSK” o “+04: 00” per ora di Mosca, ad esempio, c’è (o era) un bug MySQL dove, se impostato su un offset numerico, non regolerebbe il tempo di risparmio di ora legale

Per ottenere il fuso orario corrente

 SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP); 

Restituirà 02:00:00 se il tuo fuso orario è +2: 00.

Per ottenere il timestamp UNIX corrente (in secondi):

 SELECT UNIX_TIMESTAMP(NOW()); SELECT UNIX_TIMESTAMP(); 

Per ottenere la colonna timestamp come timestamp UNIX

 SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name` 

Per ottenere una colonna datetime UTC come timestamp UNIX

 SELECT UNIX_TIMESTAMP(CONVERT_TZ(`utc_datetime`, '+00:00', @@session.time_zone)) FROM `table_name` 

Ottieni un datetime del fuso orario corrente da un numero intero di timestamp UNIX positivo

 SELECT FROM_UNIXTIME(`unix_timestamp_int`) FROM `table_name` 

Ottieni un datetime UTC da un timestamp UNIX

 SELECT CONVERT_TZ(FROM_UNIXTIME(`unix_timestamp_int`), @@session.time_zone, '+00:00') FROM `table_name` 

Ottieni un datetime del fuso orario corrente da un numero intero di timestamp UNIX negativo

 SELECT DATE_ADD('1970-01-01 00:00:00',INTERVAL -957632400 SECOND) 

Ci sono 3 luoghi in cui il fuso orario potrebbe essere impostato in MySQL:

Nota: un fuso orario può essere impostato in 2 formati:

  1. uno scostamento da UTC: ‘+00: 00’, ‘+10: 00’ o ‘-6: 00’
  2. come fuso orario specificato: “Europa / Helsinki”, “Stati Uniti / Est” o “MET”

I fusi orari denominati possono essere utilizzati solo se le tabelle di informazioni sul fuso orario nel database mysql sono state create e popolate.

nel file “my.cnf”

 default_time_zone='+00:00' 

o

 timezone='UTC' 

@@ variabile global.time_zone

Per vedere a quale valore sono impostati

 SELECT @@global.time_zone; 

Per impostare un valore, usa uno dei due:

 SET GLOBAL time_zone = '+8:00'; SET GLOBAL time_zone = 'Europe/Helsinki'; SET @@global.time_zone='+00:00'; 

@@ session.time_zone variable

 SELECT @@session.time_zone; 

Per impostarlo utilizzare uno dei due:

 SET time_zone = 'Europe/Helsinki'; SET time_zone = "+00:00"; SET @@session.time_zone = "+00:00"; 

sia “@@ global.time_zone variable” che “@@ session.time_zone variable” potrebbero restituire “SYSTEM”, il che significa che usano il fuso orario impostato in “my.cnf”.

Affinché i nomi dei fusi orari funzionino (anche per fuso orario predefinito), è necessario impostare le tabelle delle informazioni sul fuso orario che devono essere compilate: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support. html

Nota: non puoi farlo poiché restituirà NULL:

 SELECT CONVERT_TZ(`timestamp_field`, TIMEDIFF(NOW(), UTC_TIMESTAMP), '+00:00') AS `utc_datetime` FROM `table_name` 

Imposta le tabelle del fuso orario mysql

Affinché CONVERT_TZ funzioni, è necessario compilare le tabelle del fuso orario

 SELECT * FROM mysql.`time_zone` ; SELECT * FROM mysql.`time_zone_leap_second` ; SELECT * FROM mysql.`time_zone_name` ; SELECT * FROM mysql.`time_zone_transition` ; SELECT * FROM mysql.`time_zone_transition_type` ; 

Se sono vuoti, riempili eseguendo questo comando

 mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql 

se questo comando ti dà l’errore ” data troppo lunga per l’abbreviazione di colonna” nella riga 1 “, allora potrebbe essere causato da un carattere NULL aggiunto alla fine dell’abbreviazione del fuso orario

la soluzione è quella di eseguire questo

 mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root -p mysql (if the above gives error "data too long for column 'abbreviation' at row 1") mysql_tzinfo_to_sql /usr/share/zoneinfo > /tmp/zut.sql echo "SET SESSION SQL_MODE = '';" > /tmp/mysql_tzinfo_to.sql cat /tmp/zut.sql >> /tmp/mysql_tzinfo_to.sql mysql --defaults-file=/etc/mysql/my.cnf --user=verifiedscratch -p mysql < /tmp/mysql_tzinfo_to.sql 

(assicurati che i tuoi server dst rules siano aggiornati zdump -v Europe/Moscow | grep 2011 https://chrisjean.com/updating-daylight-saving-time-on-linux/ )

Visualizza la cronologia di transizione completa dell'ora legale (ora legale) per ogni fuso orario

 SELECT tzn.Name AS tz_name, tztt.Abbreviation AS tz_abbr, tztt.Is_DST AS is_dst, tztt.`Offset` AS `offset`, DATE_ADD('1970-01-01 00:00:00',INTERVAL tzt.Transition_time SECOND) AS transition_date FROM mysql.`time_zone_transition` tzt INNER JOIN mysql.`time_zone_transition_type` tztt USING(Time_zone_id, Transition_type_id) INNER JOIN mysql.`time_zone_name` tzn USING(Time_zone_id) -- WHERE tzn.Name LIKE 'Europe/Moscow' -- Moscow has weird DST changes ORDER BY tzt.Transition_time ASC 

CONVERT_TZ applica inoltre tutte le modifiche necessarie CONVERT_TZ legale in base alle regole delle tabelle precedenti e alla data che si utilizza.

Nota:
Secondo i documenti , il valore impostato per time_zone non cambia, se lo si imposta come "+01: 00" ad esempio, quindi time_zone verrà impostato come offset rispetto a UTC, che non segue l'ora legale, quindi lo farà rimanere lo stesso tutto l'anno.

Solo i fusi orari specificati cambieranno il tempo durante l'ora legale.

Le abbreviazioni come CET saranno sempre un orario invernale e CEST sarà l'ora legale mentre +01: 00 sarà sempre l'ora UTC + 1 ora e entrambe non cambieranno con l'ora legale.

Il fuso orario del system sarà il fuso orario della macchina host in cui è installato mysql (a meno che mysql non riesca a determinarlo)

Puoi leggere ulteriori informazioni su come lavorare con l'ora legale qui

domande correlate:

  • Come posso impostare il fuso orario di MySQL?
  • MySql - Seleziona la colonna TimeStamp in formato UTC
  • Come ottenere il timestamp Unix in MySQL dall'ora UTC?
  • Conversione da server MySQL TimeStamp a UTC
  • https://dba.stackexchange.com/questions/20217/mysql-set-utc-time-as-default-timestamp
  • Come posso ottenere il fuso orario corrente di MySQL?
  • Campi datetime di MySQL e ora legale - come faccio a fare riferimento all'ora "extra"?
  • Conversione di valori negativi da FROM_UNIXTIME

fonti:

PHP e MySQL hanno le loro configurazioni di fuso orario predefinite. Dovresti sincronizzare il tempo tra il tuo database e l’applicazione web, altrimenti potresti avere qualche problema.

Leggi questo tutorial: Come sincronizzare i tuoi fusi orari PHP e MySQL

I pro e i contro sono praticamente identici. Dipende se lo vuoi o no.

Fai attenzione, se il fuso orario MySQL differisce dal tuo tempo di sistema (ad esempio PHP), confrontando il tempo o la stampa con l’utente comporterà qualche ritouch.