Pola datetime MySQL i czas letni-jak odwołać się do" dodatkowej " godziny?

Wymyśliłem to dla swoich celów. Podsumuję to, czego się nauczyłem(przepraszam, te notatki są gadatliwe; są tak samo dla mojego przyszłego skierowania, jak cokolwiek innego).

W przeciwieństwie do tego, co powiedziałem w jednym z moich poprzednich komentarzy, pola DATETIME i TIMESTAMP zachowują się inaczej. Pola znacznika czasu (jak wskazują dokumenty) przyjmują dowolne dane wysłane w formacie "RRRR-MM-DD hh:mm:ss" i konwertują je z bieżącej strefy czasowej na czas UTC. Odwrotność dzieje się transparentnie za każdym razem, gdy odzyskasz dane. Pola DATETIME nie dokonują 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, która przestrzega DST. Jeśli przechowujesz "2009-11-01 01:30:00" pola nie mają możliwości rozróżnienia, którą wersję z 1: 30 rano chcesz-Wersja -04: 00 lub -05: 00.

Ok, więc musimy przechowywać nasze dane w strefie czasowej non DST(takiej jak UTC). Pola znacznika czasu nie są w stanie dokładnie obsłużyć tych danych z powodów wyjaśnię: jeśli system jest ustawiony na strefę czasową DST, to co umieścisz w znaczniku czasu może nie być tym, co otrzymasz. Nawet jeśli wyślesz dane, które już przekonwertowałeś NA UTC, nadal będzie zakładał, że dane są w Twojej lokalnej strefie czasowej i przeprowadzi kolejną konwersję na UTC. Ten znacznik czasu-wymuszony lokalny-do-UTC-back-to-local roundtrip jest stratny, gdy Twoja lokalna Strefa czasowa przestrzega czasu DST (od "2009-11-01 01:30:00" maps to 2 różne możliwe czasy).

Dzięki DATETIME możesz przechowywać swoje dane w dowolnej strefie czasowej i mieć pewność, że otrzymasz to, co je wyślesz (nie zmuszasz się do stratnych konwersji w obie strony, które pola znacznika czasu są na Ciebie rzucane). Rozwiązaniem jest użycie pola DATETIME i przed zapisaniem do pola konwersja z systemowej strefy czasowej na dowolną strefę inną niż DST, w której chcesz ją zapisać (myślę, że UTC jest prawdopodobnie najlepszą opcją). Pozwala to na zbudowanie konwersja logiki na język skryptowy, 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".

Kolejną ważną rzeczą jest to, że funkcje matematyczne daty/czasu MySQL nie działają poprawnie wokół granic DST, jeśli przechowujesz daty w DST TZ. Więc tym bardziej powód, aby zapisać w UTC.

W skrócie robię to:

Podczas pobierania danych z bazy danych:

Jawnie interpretować dane z bazy danych jako UTC poza MySQL w celu uzyskania dokładnego Unix timestamp. Używam do tego funkcji strtotime () PHP lub jej klasy DateTime. Nie można tego niezawodnie zrobić wewnątrz MySQL za pomocą funkcji Convert_tz() lub UNIX_TIMESTAMP() MySQL, ponieważ CONVERT_TZ wyświetli tylko wartość 'YYYY-MM-DD hh:mm:ss', która ma problemy z niejednoznacznością, a UNIX_TIMESTAMP() zakłada, że jej wejście znajduje się w strefie czasowej systemu, a nie w strefie czasowej, w której dane zostały faktycznie zapisane (UTC).

Podczas przechowywania danych do bazy danych:

Konwertuj datę na dokładny czas UTC, który chcesz poza MySQL. Na przykład:w PHP klasy DateTime można określić "2009-11-01 1:30:00 EST" wyraźnie od "2009-11-01 1:30: 00 EDT", a następnie przekonwertować go na UTC i zapisać poprawny czas UTC w polu DATETIME.

Phew. Wielkie dzięki za wkład i pomoc. Mam nadzieję, że to oszczędzi komuś bólu głowy.

BTW, jestem zobacz to na MySQL 5.0.22 i 5.0.27

Myślę, że link micahwittmana ma najlepsze praktyczne rozwiązanie dla tych ograniczeń MySQL: Ustaw strefę czasową sesji NA UTC po połączeniu:

SET SESSION time_zone = '+0:00'

Potem wystarczy wysłać uniksowe znaczniki czasu i wszystko powinno być w porządku.

Ale jak mam coś 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 konwertować z UTC na localtime używając CONVERT_TZ:

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

Gdzie ' + 00: 00 'to UTC, Strefa czasowa from , a' SYSTEM ' to lokalna Strefa czasowa systemu operacyjnego, na którym działa MySQL.

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

Gdyby ktoś się zastanawiał, w tym przypadku TIMESTAMP zachowuj się poprawnie i możesz odróżnić dwie podobne daty, konwertując TIMESTAMP na uniksowy znacznik czasu:

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

Pracowałem nad rejestrowaniem liczby odwiedzin stron i wyświetlaniem liczby w grafie (za pomocą 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 o jeden dzień wolne według etykiet na osi X. Po badaniu zauważyłem, że liczba widoków na dzień 2015-10-25 została pobrana dwa razy z bazy i przekazana do Flot, więc każdego dnia po tej dacie była przesuwana o jeden dzień w prawo.
Po szukaniu pluskwy w moim kodzie przez chwilę zdałem sobie sprawę, że ta data jest, gdy DST ma miejsce. Potem doszedłem do tej strony...
...ale sugerowane rozwiązania były przesadą dla tego, czego potrzebowałem lub miały inne wady. nie martwię się zbytnio tym, że nie jestem w stanie rozróżnić dwuznacznych znaczników czasu. muszę tylko policzyć i wyświetlić rekordy na 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ącej się od min_date, kończącej się na max_date, za pomocą kroku pewnego dnia (60*60*24) odzyskuję liczniki:

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
}

Moje ostateczne i szybkie rozwiązanie do mój problem był taki:

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

Więc jestem nie wpatrując się w pętlę na początku dnia, ale dwie godziny później. Dzień jest wciąż ten sam, a ja wciąż pobieram poprawne zliczenia, ponieważ wyraźnie proszę bazę danych o rekordy między 00:00:00 a 23: 59: 59 dnia, niezależnie od rzeczywistego czasu znacznika czasu. A kiedy czas skacze o godzinę, jestem jeszcze we właściwym dniu.

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