Czy MySQL powinien mieć strefę czasową ustawioną na UTC?


149

Dalsze pytanie dotyczące /server/191331/should-servers-have-their-timezone-set-to-gmt-utc

Czy strefa czasowa MySQL powinna być ustawiona na UTC, czy też powinna być ustawiona na tę samą strefę czasową, co serwer lub PHP? (Jeśli nie jest to UTC)

Jakie są wady i zalety?


stackoverflow.com/a/1650406/175071 podaje dobre powody, dla których warto korzystać z UTC
Timo Huovinen

UTC nie jest strefą czasową. UTC to standard, GMT to strefa czasowa. zachholman.com/talk/utc-is-enough-for-everyone-right
agoldev

Kolejny świetny powód do korzystania z UTC dba.stackexchange.com/questions/161416/…
Timo Huovinen

Odpowiedzi:


533

Wydaje się, że nie ma znaczenia, która strefa czasowa znajduje się na serwerze, o ile masz ustawiony czas dla bieżącej strefy czasowej, znasz strefę czasową kolumn z datą i godziną, które przechowujesz, i jesteś świadomy problemów z czasem letnim.

Z drugiej strony, jeśli masz kontrolę nad strefami czasowymi serwerów, z którymi pracujesz, możesz ustawić wszystko wewnętrznie na UTC i nigdy nie martwić się o strefy czasowe i czas letni.

Oto kilka uwag, które zebrałem, jak pracować ze strefami czasowymi jako formą ściągawki dla siebie i innych, które mogą mieć wpływ na to, jaką strefę czasową dana osoba wybierze dla swojego serwera i jak będzie przechowywać datę i godzinę.

Ściągawka dotycząca strefy czasowej MySQL

Uwagi:

  1. Zmiana strefy czasowej nie zmieni zapisanej daty i godziny ani sygnatury czasowej , ale wybierze inną datę i godzinę z kolumn datownika
  2. Ostrzeżenie! UTC ma sekundy przestępne, wyglądają jak „2012-06-30 23:59:60” i mogą być dodawane losowo, z 6-miesięcznym wyprzedzeniem, ze względu na spowolnienie obrotu Ziemi
  3. GMT myli sekundy, dlatego wynaleziono UTC.

  4. Ostrzeżenie! różne regionalne strefy czasowe mogą generować tę samą wartość daty i godziny ze względu na czas letni

  5. Kolumna sygnatura czasowa obsługuje tylko daty od 1970-01-01 00:00:01 do 2038-01-19 03:14:07 UTC ze względu na ograniczenie .
  6. Wewnętrznie kolumna sygnatury czasowej MySQL jest przechowywana jako UTC, ale po wybraniu daty MySQL automatycznie przekonwertuje ją na strefę czasową bieżącej sesji.

    Przechowując datę w znaczniku czasowym, MySQL przyjmie, że data jest w strefie czasowej bieżącej sesji i przekonwertuje ją na czas UTC w celu przechowywania.

  7. MySQL może przechowywać częściowe daty w kolumnach z datą i godziną, wyglądają jak „2013-00-00 04:00:00”
  8. MySQL przechowuje „0000-00-00 00:00:00”, jeśli ustawisz kolumnę z datą i godziną na NULL, chyba że specjalnie ustawisz kolumnę tak, aby zezwalała na wartość null podczas jej tworzenia.
  9. Przeczytaj to

Aby wybrać kolumnę znacznika czasu w formacie UTC

bez względu na strefę czasową, w której znajduje się bieżąca sesja MySQL:

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

Możesz również ustawić serwer lub strefę czasową globalną lub bieżącą sesji na UTC, a następnie wybrać sygnaturę czasową w następujący sposób:

SELECT `timestamp_field` FROM `table_name`

Aby wybrać bieżącą datę i godzinę w UTC:

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

Przykładowy wynik: 2015-03-24 17:02:41

Aby wybrać bieżącą datę i godzinę w strefie czasowej sesji

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

Aby wybrać strefę czasową, która została ustawiona podczas uruchamiania serwera

SELECT @@system_time_zone;

Zwraca „MSK” lub „+04: 00” dla czasu moskiewskiego, na przykład wystąpił (lub był) błąd MySQL, który powodował, że ustawienie przesunięcia liczbowego nie powodowało dostosowania czasu letniego

Aby uzyskać aktualną strefę czasową

SELECT TIMEDIFF(NOW(), UTC_TIMESTAMP);

Zwróci 02:00:00, jeśli Twoja strefa czasowa to +2: 00.

Aby uzyskać aktualny znacznik czasu UNIX (w sekundach):

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

Aby uzyskać kolumnę timestamp jako znacznik czasu UNIX

SELECT UNIX_TIMESTAMP(`timestamp`) FROM `table_name`

Aby uzyskać kolumnę z datą i godziną UTC jako znacznik czasu systemu UNIX

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

Uzyskaj aktualną datę i godzinę strefy czasowej z dodatniej liczby całkowitej znacznika czasu systemu UNIX

SELECT FROM_UNIXTIME(`unix_timestamp_int`) FROM `table_name`

Uzyskaj datę i godzinę UTC ze znacznika czasu UNIX

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

Uzyskaj bieżącą datę i godzinę strefy czasowej z ujemnej liczby całkowitej znacznika czasu systemu UNIX

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

Istnieją 3 miejsca, w których strefa czasowa może być ustawiona w MySQL:

Uwaga: strefę czasową można ustawić w 2 formatach:

  1. przesunięcie względem UTC: „+00: 00”, „+10: 00” lub „-6: 00”
  2. jako nazwana strefa czasowa: „Europa / Helsinki”, „USA / Wschód” lub „MET”

Nazwane strefy czasowe mogą być używane tylko wtedy, gdy tabele informacji o strefach czasowych w bazie danych mysql zostały utworzone i wypełnione.

w pliku „my.cnf”

default_time_zone='+00:00'

lub

timezone='UTC'

@@ zmienna global.time_zone

Aby zobaczyć, jaką wartość mają

SELECT @@global.time_zone;

Aby ustawić wartość, użyj jednej z nich:

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

@@ session.time_zone zmienna

SELECT @@session.time_zone;

Aby ustawić, użyj jednego z nich:

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

Zarówno „@@ global.time_zone variable”, jak i „@@ session.time_zone variable” mogą zwrócić „SYSTEM”, co oznacza, że ​​używają strefy czasowej ustawionej w „my.cnf”.

Aby nazwy stref czasowych działały (nawet dla domyślnej strefy czasowej), musisz skonfigurować tabele informacji o strefach czasowych, które muszą być wypełnione: http://dev.mysql.com/doc/refman/5.1/en/time-zone-support. html

Uwaga: nie możesz tego zrobić, ponieważ zwróci NULL:

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

Skonfiguruj tabele stref czasowych mysql

Aby CONVERT_TZdziałało, musisz wypełnić tabele stref czasowych

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` ;

Jeśli są puste, wypełnij je, uruchamiając to polecenie

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

jeśli to polecenie wyświetli błąd „ dane za długie dla skrótu kolumny w wierszu 1 ”, może to być spowodowane dodaniem znaku NULL na końcu skrótu strefy czasowej

rozwiązaniem jest uruchomienie tego

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

(upewnij się, że zasady dotyczące czasu letniego serwerów są aktualne zdump -v Europe/Moscow | grep 2011 https://chrisjean.com/updating-daylight-saving-time-on-linux/ )

Zobacz pełną historię zmian czasu letniego (DST) dla każdej strefy czasowej

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 stosuje również wszelkie niezbędne zmiany czasu letniego w oparciu o zasady podane w powyższych tabelach i wykorzystaną datę.

Uwaga:
zgodnie z dokumentacją wartość ustawiona dla strefy czasowej nie zmienia się, jeśli ustawisz ją na przykład jako „+01: 00”, wówczas strefa_czasowa zostanie ustawiona jako przesunięcie względem czasu UTC, który nie jest zgodny z czasem letnim, więc pozostanie niezmieniony przez cały rok.

Tylko nazwane strefy czasowe zmienią czas w czasie letnim.

Skróty takie jak CETzawsze będą oznaczać czas zimowy i czas CESTletni, podczas gdy +01: 00 zawsze będzie oznaczać UTCczas + 1 godzinę i oba nie zmienią się wraz z czasem letnim .

Strefa systemczasowa będzie strefą czasową komputera hosta, na którym jest zainstalowany mysql (chyba że mysql tego nie określi)

Możesz przeczytać więcej o pracy z czasem letnim tutaj

Powiązane pytania:

Źródła:


Więc jeśli mam ustawiony typ kolumny na znacznik czasu. A moja strefa czasowa to +12: 00 i chcę zaktualizować kolumnę za pomocą daty / godziny opartej na utc, czy istnieje sposób na uwzględnienie strefy czasowej w instrukcji aktualizacji, czy też powinienem używać funkcji convert_tz. Na przykład. tablezestaw aktualizacji modified= '2016-07-07 08:10 +00: 00'
bumperbox

2
@bumperbox mysql zawsze zakłada, że ​​data, którą podajesz w kolumnie timestamp, jest w tej samej strefie czasowej co serwer mysql, więc musisz przekonwertować datę ze strefy czasowej +12: 00 na strefę czasową serwera mysql w celu aktualizacji. Dlatego używam UTC na serwerze mysql i konwertuję dowolną datę na UTC przed jej zapisaniem.
Timo Huovinen

5
Jedna z najlepszych i najbardziej pouczających odpowiedzi, na jakie natknąłem się przez lata używania SO. Dziękuję Ci.
Mitya

OSTRZEŻENIE!!! Jakiekolwiek użycie lub konwersje z czasu lokalnego w strefie czasowej DST będą błędne o godzinę na godzinę każdego roku pod koniec czasu letniego. Dotyczy to UNIX_TIMESTAMP(NOW());również wszystkich zastosowań, w CONVERT_TZ()których jednym z parametrów jest `@@ sesja.czas_zone. Aby niezawodnie konwertować czasy danych UTC na znaczniki czasu UNIX, w zasadzie należy najpierw ustawić strefę_czasu sesji.
Doin

1
@Flimm całkowicie w porządku, zapomniałem to naprawić jakiś czas temu.
Timo Huovinen

3

Oto działający przykład:

jdbc:mysql://localhost:3306/database?useUnicode=yes&characterEncoding=UTF-8&serverTimezone=Europe/Moscow

2

PHP i MySQL mają własne domyślne konfiguracje stref czasowych. Powinieneś zsynchronizować czas między bazą danych a aplikacją internetową, w przeciwnym razie możesz uruchomić pewne problemy.

Przeczytaj ten samouczek: Jak zsynchronizować strefy czasowe PHP i MySQL


To w zasadzie dwie linie kodu: date_default_timezone_set("America/Los_Angeles");i mysql_query("SET time_zone='" . date('P', time()) . "'");działa bardzo elegancko!
Noumenon

3
@Noumenon Ostrożnie z tym! Dziś rano drapałem się po głowie, ponieważ właśnie to robiłem, a niektóre moje czasy minęły o godzinę. Podejrzewam, że użycie nazwanej strefy czasowej jest dokładniejsze, gdy w grę wchodzi czas letni. Jeśli używasz America / New_York, MySQL zna czas letni i odpowiednio przechowuje daty. Jeśli po prostu ustawisz ją na -04: 00, tak jak tutaj, nie będzie uwzględniać obliczenia czasu letniego.
Nathanb

1
Sprawdź, czy mysql poprawnie wie o czasie letnim, reguły czasu letniego są regularnie aktualizowane, a powiązane tabele mysql również wymagają aktualizacji (patrz powyżej na dole mojej odpowiedzi)
Timo Huovinen

1

Wady i zalety są prawie identyczne, zależy to od tego, czy tego chcesz, czy nie.

Uważaj, jeśli strefa czasowa MySQL różni się od czasu systemowego (na przykład PHP), porównywanie czasu lub drukowania do użytkownika będzie wymagało trochę majsterkowania.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.