MySQL
Gestione dei fusi orari
Ricerca…
Osservazioni
Quando hai bisogno di gestire le informazioni sul tempo per una base di utenti in tutto il mondo in MySQL, usa il tipo di dati TIMESTAMP nelle tue tabelle.
Per ogni utente, memorizzare una colonna del fuso orario delle preferenze dell'utente. VARCHAR (64) è un buon tipo di dati per quella colonna. Quando un utente si registra per utilizzare il sistema, chiedere il valore del fuso orario. Il mio è Atlantic Time, America/Edmonton . Il vostro potrebbe o potrebbe non essere l' Asia/Kolkata o l' Australia/NSW . Per un'interfaccia utente per questa impostazione delle preferenze dell'utente, il software WordPress.org ha un buon esempio.
Infine, ogni volta che si stabilisce una connessione dal proprio programma host (Java, php, qualunque) al proprio DBMS per conto di un utente, emettere il comando SQL
SET SESSION time_zone='(whatever tz string the user gave you)'
prima di gestire qualsiasi dato utente che coinvolga tempi. Quindi tutte le volte TIMESTAMP installate verranno visualizzate nell'ora locale dell'utente.
Ciò causerà la conversione di tutti i tempi nelle tue tabelle in UTC e tutte le volte che verranno tradotte in locale. Funziona correttamente per NOW () e CURDATE (). Anche in questo caso, è necessario utilizzare TIMESTAMP e non i tipi di dati DATETIME o DATE per questo.
Assicurati che il tuo sistema operativo server e i fusi orari MySQL predefiniti siano impostati su UTC. Se non lo fai prima di iniziare a caricare le informazioni nel tuo database, sarà quasi impossibile correggerle. Se si utilizza un fornitore per eseguire MySQL, insistere sul fatto che abbia ragione.
Recupera la data e l'ora correnti in un determinato fuso orario.
Questo recupera il valore di NOW() in ora locale, in India, ora standard e poi di nuovo in UTC.
SELECT NOW();
SET time_zone='Asia/Kolkata';
SELECT NOW();
SET time_zone='UTC';
SELECT NOW();
Converti un valore `DATE` o` DATETIME` memorizzato in un altro fuso orario.
Se hai un DATE o DATETIME memorizzato (in una colonna da qualche parte) è stato memorizzato rispetto ad un certo fuso orario, ma in MySQL il fuso orario non è memorizzato con il valore. Quindi, se vuoi convertirlo in un altro fuso orario, puoi, ma devi conoscere il fuso orario originale. L'utilizzo di CONVERT_TZ() esegue la conversione. Questo esempio mostra le righe vendute in California in ora locale.
SELECT CONVERT_TZ(date_sold,'UTC','America/Los_Angeles') date_sold_local
FROM sales
WHERE state_sold = 'CA'
Recupera i valori memorizzati di `TIMESTAMP` in un determinato fuso orario
Questo è veramente facile Tutti i valori TIMESTAMP sono memorizzati in tempo universale e sempre convertiti all'impostazione time_zone attuale ogni volta che vengono renderizzati.
SET SESSION time_zone='America/Los_Angeles';
SELECT timestamp_sold
FROM sales
WHERE state_sold = 'CA'
Perchè è questo? TIMESTAMP valori TIMESTAMP sono basati sul venerabile tipo di dati time_t UNIX . Questi timestamp UNIX sono memorizzati come un numero di secondi dal 1970-01-01 00:00:00 UTC.
I valori TIMESTAMP sono memorizzati nel tempo universale. DATE valori DATE e DATETIME sono memorizzati in qualunque ora locale era in vigore al momento della memorizzazione.
Qual è l'impostazione del fuso orario locale del mio server?
Ogni server ha un'impostazione globale time_zone predefinita, configurata dal proprietario della macchina server. Puoi trovare il fuso orario corrente in questo modo:
SELECT @@time_zone
Sfortunatamente, questo di solito dà il valore SYSTEM , il che significa che il tempo di MySQL è regolato dall'impostazione del fuso orario del SO del server.
Questa sequenza di query (sì, è un hack ) restituisce l'offset in minuti tra l'impostazione del fuso orario del server e l'ora UTC.
CREATE TEMPORARY TABLE times (dt DATETIME, ts TIMESTAMP);
SET time_zone = 'UTC';
INSERT INTO times VALUES(NOW(), NOW());
SET time_zone = 'SYSTEM';
SELECT dt, ts, TIMESTAMPDIFF(MINUTE, dt, ts)offset FROM times;
DROP TEMPORARY TABLE times;
Come funziona? Le due colonne nella tabella temporanea con diversi tipi di dati sono la chiave. DATETIME tipi di dati DATETIME sono sempre memorizzati in ora locale nelle tabelle e TIMESTAMP in UTC. Quindi l'istruzione INSERT , eseguita quando time_zone è impostata su UTC, memorizza due valori identici di data / ora.
Quindi, l'istruzione SELECT, viene eseguita quando time_zone è impostato sull'ora locale del server. TIMESTAMP vengono sempre tradotti dal loro modulo UTC memorizzato in ora locale nelle istruzioni SELECT. DATETIME s non lo sono. Quindi l'operazione TIMESTAMPDIFF(MINUTE...) calcola la differenza tra ora locale e universale.
Quali valori time_zone sono disponibili nel mio server?
Per ottenere un elenco dei possibili valori time_zone nell'istanza del server MySQL, utilizzare questo comando.
SELECT mysql.time_zone_name.name
Normalmente, questo mostra l' elenco ZoneInfo dei fusi orari gestito da Paul Eggert presso l' Internet Numbers Authority Assigned . In tutto il mondo ci sono approssimativamente 600 fusi orari.
Sistemi operativi simil-Unix (distribuzioni Linux, distribuzioni BSD e moderne distribuzioni Mac OS, ad esempio) ricevono aggiornamenti di routine. L'installazione di questi aggiornamenti su un sistema operativo consente alle istanze MySQL che eseguono lì di tenere traccia delle modifiche apportate ai cambi di fuso orario e di ora legale / ora solare.
Se si ottiene un elenco molto più breve di nomi di fuso orario, il server è configurato in modo incompleto o in esecuzione su Windows. Ecco le istruzioni per l'amministratore del tuo server per installare e gestire l'elenco ZoneInfo.
