Pola daty i godziny MySQL i czas letni - jak odnieść się do „dodatkowej” godziny?

Używam strefy czasowej America / New York. Jesienią „cofamy się” o godzinę - skutecznie „zyskując” godzinę o 2 nad ranem. W punkcie przejścia dzieje się co następuje:

jest 01:59:00 -04: 00,
a 1 minutę później jest to:
01:00:00 -05: 00

Więc jeśli powiesz po prostu „1:30 rano”, nie jest jasne, czy masz na myśli pierwszy raz, czy 1:30, czy drugi. Próbuję zapisać dane planowania w bazie danych MySQL i nie mogę określić, jak prawidłowo zapisać czasy.

Oto problem:
„2009-11-01 00:30:00” jest przechowywany wewnętrznie jako 2009-11-01 00:30:00 -04: 00
„2009-11-01 01:30:00” jest przechowywany wewnętrznie jako 2009-11-01 01:30:00 -05: 00

To jest w porządku i dość oczekiwane. Ale jak mam cokolwiek zapisać do 01:30:00 -04: 00 ? Dokumentacja nie wykazuje żadnego wsparcia dla określenia przesunięcia i, odpowiednio, gdy próbowałem określające przesunięcie to zostało należycie ignorowane.

Jedyne rozwiązania, o których myślałem, obejmują ustawienie serwera na strefę czasową, która nie wykorzystuje czasu letniego i wykonanie niezbędnych transformacji w moich skryptach (używam do tego PHP). Ale to nie wydaje się być konieczne.

Wielkie dzięki za wszelkie sugestie.

Typy dat MySQL są, szczerze mówiąc, zepsute i nie mogą być przechowywane poprawnie przez cały czas, chyba że twój system jest ustawiony na stałą strefę czasową przesunięcia, jak UTC lub GMT-5. (Używam MySQL 5.0.45)

Dzieje się tak, ponieważ nie można zapisać żadnego czasu w ciągu godziny przed zakończeniem czasu letniego . Bez względu na to, w jaki sposób wprowadzisz daty, każda funkcja daty potraktuje te godziny tak, jakby przypadały na godzinę po przełączeniu.

Strefa czasowa mojego systemu to America/New_York. Spróbujmy zapisać 1257051600 (niedziela, 01 listopada 2009 06:00:00 +0100).

Oto użycie zastrzeżonej składni INTERVAL:

SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3599 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 00:00:00' + INTERVAL 3600 SECOND); # 1257055200

SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 1 SECOND); # 1257051599
SELECT UNIX_TIMESTAMP('2009-11-01 01:00:00' - INTERVAL 0 SECOND); # 1257055200

Nawet FROM_UNIXTIME()nie zwróci dokładnego czasu.

SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051599)); # 1257051599
SELECT UNIX_TIMESTAMP(FROM_UNIXTIME(1257051600)); # 1257055200

Co dziwne, DATETIME nadal będzie przechowywać i zwracać (tylko w postaci ciągu!) Czasy w „utraconej” godzinie, kiedy zaczyna się czas letni (np 2009-03-08 02:59:59.). Ale używanie tych dat w dowolnej funkcji MySQL jest ryzykowne:

SELECT UNIX_TIMESTAMP('2009-03-08 01:59:59'); # 1236495599
SELECT UNIX_TIMESTAMP('2009-03-08 02:00:00'); # 1236495600
# ...
SELECT UNIX_TIMESTAMP('2009-03-08 02:59:59'); # 1236495600
SELECT UNIX_TIMESTAMP('2009-03-08 03:00:00'); # 1236495600

Na wynos: jeśli musisz przechowywać i pobierać za każdym razem w roku, masz kilka niepożądanych opcji:

1. Ustaw strefę czasową systemu na GMT + pewne stałe przesunięcie. Np. UTC

2. Przechowuj daty jako INT (jak odkrył Aaron, TIMESTAMP nie jest nawet wiarygodny)

3. Udawaj, że typ DATETIME ma stałą strefę czasową przesunięcia. Np. Jeśli jesteś w America/New_Yorkśrodku, przekonwertuj swoją datę na GMT-5 poza MySQL , a następnie zapisz jako DATETIME (to okazuje się być niezbędne: zobacz odpowiedź Aarona). W takim razie musisz bardzo uważać, używając funkcji daty / czasu MySQL, ponieważ niektóre zakładają, że twoje wartości są z systemowej strefy czasowej, inne (w szczególności funkcje arytmetyczne dotyczące czasu) są "niezależne od strefy czasowej" (mogą zachowywać się tak, jakby czasy były UTC).

Aaron i ja podejrzewamy, że automatycznie generujące się kolumny TIMESTAMP również nie działają. Oba 2009-11-01 01:30 -0400i 2009-11-01 01:30 -0500będą przechowywane jako niejednoznaczne 2009-11-01 01:30.

Rozgryzłem to do swoich celów. Podsumuję, czego się nauczyłem (przepraszam, te notatki są rozwlekłe; są tak samo ważne dla mojego przyszłego polecenia, jak cokolwiek innego).

Wbrew temu, co powiedziałem w jednym z moich poprzednich komentarzach, pola datetime i godziną należy zachowywać się inaczej. Pola TIMESTAMP (jak wskazują dokumenty) pobierają wszystko, co je wyślesz w formacie „RRRR-MM-DD gg: mm: ss” i konwertują z aktualnej strefy czasowej na czas UTC. Odwrotna sytuacja dzieje się w sposób przejrzysty za każdym razem, gdy pobierasz dane. Pola DATETIME nie powodują tej konwersji. Biorą wszystko, co im wyślesz i po prostu przechowują to bezpośrednio.

Ani typy pól DATETIME, ani TIMESTAMP nie mogą dokładnie przechowywać danych w strefie czasowej, w której obserwuje się czas letni . Jeśli zapiszesz „2009-11-01 01:30:00”, pola nie będą miały możliwości rozróżnienia, którą wersję z 1:30 chcesz - wersję -04: 00 lub -05: 00.

Ok, więc musimy przechowywać nasze dane w strefie czasowej innej niż DST (np. UTC). Pola TIMESTAMP nie mogą dokładnie obsłużyć tych danych z powodów, które wyjaśnię: jeśli twój system jest ustawiony na strefę czasową DST, to to, co umieścisz w TIMESTAMP, może nie być tym, co otrzymujesz. Nawet jeśli wyślesz dane, które już przekonwertowałeś na UTC, nadal przyjmie dane z Twojej lokalnej strefy czasowej i dokona kolejnej konwersji na UTC. Ta wymuszona przez TIMESTAMP podróż w obie strony lokalna do czasu UTC z powrotem do lokalnego jest stratna, gdy lokalna strefa czasowa obserwuje czas letni (od „2009-11-01 01:30:00” odwzorowuje 2 różne możliwe czasy).

Dzięki DATETIME możesz przechowywać dane w dowolnej strefie czasowej i mieć pewność, że odzyskasz wszystko, co wyślesz (nie zostaniesz zmuszony do stratnych konwersji w obie strony, które narzucają Ci pola TIMESTAMP). Tak więc rozwiązaniem jest użycie pola DATETIME i przed zapisaniem w polu przekonwertowanie ze strefy czasowej systemu na dowolną strefę inną niż DST, w której chcesz ją zapisać (myślę, że UTC jest prawdopodobnie najlepszą opcją). Pozwala to na zbudowanie logiki konwersji w języku skryptów, dzięki czemu można jawnie zapisać odpowiednik UTC „2009-11-01 01:30:00 -04: 00” lub „” 2009-11-01 01:30: 00 -05: 00 ”.

Inną ważną rzeczą, na którą należy zwrócić uwagę, jest to, że funkcje matematyczne daty / czasu MySQL nie działają poprawnie w granicach czasu letniego, jeśli przechowujesz daty w DST TZ. Tym bardziej warto oszczędzać w UTC.

Krótko mówiąc, teraz robię to:

Podczas pobierania danych z bazy danych:

Jawnie interpretuj dane z bazy danych jako UTC poza MySQL, aby uzyskać dokładną sygnaturę czasową Uniksa. Używam do tego funkcji PHP strtotime () lub jego klasy DateTime. Nie można tego niezawodnie wykonać wewnątrz MySQL przy użyciu funkcji MySQL CONVERT_TZ () lub UNIX_TIMESTAMP (), ponieważ CONVERT_TZ wyświetli tylko wartość „RRRR-MM-DD gg: mm: ss”, która cierpi na problemy z niejednoznacznością, a UNIX_TIMESTAMP () zakłada wejście znajduje się w strefie czasowej systemu, a nie w strefie czasowej, w której dane były AKTUALNIE przechowywane w (UTC).

Podczas przechowywania danych w bazie danych:

Przekonwertuj swoją datę na dokładny czas UTC, którego potrzebujesz poza MySQL. Na przykład: w klasie PHP DateTime możesz określić „2009-11-01 1:30:00 EST” wyraźnie od „2009-11-01 1:30:00 EDT”, a następnie przekonwertować je na UTC i zapisać prawidłowy czas UTC do pola DATETIME.

Uff. Dziękuję bardzo za wkład i pomoc wszystkich. Miejmy nadzieję, że to zaoszczędzi komuś innemu bólu głowy w przyszłości.

BTW, widzę to w MySQL 5.0.22 i 5.0.27

Myślę micahwittman za ogniwo ma najlepsze praktyczne rozwiązanie tych ograniczeń MySQL: Ustaw strefę czasową sesji UTC po podłączeniu:

SET SESSION time_zone = '+0:00'

Następnie po prostu wyślij mu znaczniki czasu Unix i wszystko powinno być w porządku.

Ten wątek sprawił, że zwariowałem, ponieważ używamy TIMESTAMPkolumn z On UPDATE CURRENT_TIMESTAMP(tj recordTimestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP:) do śledzenia zmienionych rekordów i ETL do magazynu danych.

W przypadku gdyby ktoś się zastanawiał, w tym przypadku TIMESTAMPzachowaj się poprawnie i możesz odróżnić dwie podobne daty, konwertując na TIMESTAMPunix timestamp:

select TestFact.*, UNIX_TIMESTAMP(recordTimestamp) from TestFact;

id  recordTimestamp         UNIX_TIMESTAMP(recordTimestamp)
1   2012-11-04 01:00:10.0   1352005210
2   2012-11-04 01:00:10.0   1352008810

Ale jak mam cokolwiek zapisać do 01:30:00 -04: 00?

Możesz przekonwertować na UTC jak:

SELECT CONVERT_TZ('2009-11-29 01:30:00','-04:00','+00:00');

Możesz przekonwertować czas UTC na czas lokalny za pomocą CONVERT_TZ :

SELECT CONVERT_TZ(UTC_TIMESTAMP(),'+00:00','SYSTEM');

Gdzie „+00: 00” to UTC, strefa czasowa od, a „SYSTEM” to lokalna strefa czasowa systemu operacyjnego, w którym działa MySQL.

MySQL z natury rozwiązuje ten problem za pomocą tabeli time_zone_name z bazy danych mysql. Użyj CONVERT_TZ podczas CRUD, aby zaktualizować datę i godzinę bez martwienia się o czas letni.

SELECT
  CONVERT_TZ('2019-04-01 00:00:00','Europe/London','UTC') AS time1,
  CONVERT_TZ('2019-03-01 00:00:00','Europe/London','UTC') AS time2;

Pracowałem nad logowaniem liczby odwiedzin stron i wyświetlaniem ich na wykresie (przy użyciu wtyczki Flot jQuery). Wypełniłem tabelę danymi testowymi i wszystko wyglądało dobrze, ale zauważyłem, że na końcu wykresu punkty były jeden dzień wolne zgodnie z etykietami na osi X. Po zbadaniu zauważyłem, że liczba wyświetleń na dzień 2015-10-25 była dwukrotnie pobierana z bazy danych i przekazywana do Flota, więc każdy dzień po tej dacie był przesuwany o jeden dzień w prawo.
Po pewnym czasie szukania błędu w moim kodzie zdałem sobie sprawę, że to data, kiedy ma miejsce czas letni. Potem doszedłem do tej strony SO ...
... ale sugerowane rozwiązania były przesadą w stosunku do tego, czego potrzebowałem, lub miały inne wady. Nie martwię się zbytnio, że nie będę w stanie rozróżniać niejednoznacznych znaczników czasu. Muszę tylko policzyć i wyświetlić rekordy w ciągu dni.

Najpierw pobieram zakres dat:

SELECT 
    DATE(MIN(created_timestamp)) AS min_date, 
    DATE(MAX(created_timestamp)) AS max_date 
FROM page_display_log
WHERE item_id = :item_id

Następnie w pętli for, zaczynając od min_date, kończąc na max_date, o krok jednego dnia ( 60*60*24), pobieram liczby:

for( $day = $min_date_timestamp; $day <= $max_date_timestamp; $day += 60 * 60 * 24 ) {
    $query = "
        SELECT COUNT(*) AS count_per_day
        FROM page_display_log
        WHERE 
            item_id = :item_id AND
            ( 
                created_timestamp BETWEEN 
                '" . date( "Y-m-d 00:00:00", $day ) . "' AND
                '" . date( "Y-m-d 23:59:59", $day ) . "'
            )
    ";
    //execute query and do stuff with the result
}

Moja ostateczna i szybkie rozwiązanie do mojego problemu było to:

$min_date_timestamp += 60 * 60 * 2; // To avoid DST problems
for( $day = $min_date_timestamp; $day <= $max_da.....

Więc nie patrzę w pętlę na początku dnia, ale dwie godziny później . Dzień jest nadal ten sam i nadal pobieram prawidłowe liczniki, ponieważ wyraźnie proszę bazę danych o rekordy między 00:00:00 a 23:59:59 dnia, niezależnie od faktycznego czasu znacznika czasu. A kiedy czas skacze o jedną godzinę, nadal jestem we właściwym dniu.

Uwaga: wiem, że to jest 5-letni wątek i wiem, że to nie jest odpowiedź na pytanie OP, ale może pomóc osobom takim jak ja, które napotkały tę stronę, szukając rozwiązania opisanego przeze mnie problemu.