Czy powinienem używać typu danych datetime lub timestamp w MySQL?

W MySQL 5 i nowszych, wartości TIMESTAMP są konwertowane z bieżącej strefy czasowej na UTC do przechowywania i konwertowane z powrotem z UTC do bieżącej strefy czasowej do pobierania. (Występuje tylko dla typu danych TIMESTAMP, a NIE dla innych typów, takich jak DATETIME.)

Domyślnie, bieżącą strefą czasową dla każdego połączenia jest czas serwera. Strefa czasowa może być ustawiona na zasadzie per-connection, jak opisano w Obsługa strefy czasowej serwera MySQL.

Zawsze używam pól DATETIME do niczego innego niż metadane wiersza (Data utworzona lub zmodyfikowana).

Jako wymienione w dokumentacji MySQL:

Typ DATETIME jest używany, gdy potrzebujesz wartości, które zawierają zarówno informacje o dacie, jak i czasie. MySQL pobiera i wyświetla wartości DATETIME w formacie 'YYYY-MM-DD HH: MM: SS'. Obsługiwany zakres to "1000-01-01 00: 00: 00" do "9999-12-31 23: 59: 59".

...

Typ danych TIMESTAMP ma zakres "1970-01-01 00: 00: 01" UTC do "2038-01-09 03: 14: 07" UTC. Ma różne właściwości, w zależności od wersji MySQL i trybu SQL, w którym serwer jest uruchomiony.

Prawdopodobnie przekroczysz dolny limit znaczników czasu w ogólnym użyciu - np. przechowując datę urodzenia.

Poniższe przykłady pokazują, jak typ TIMESTAMP Date zmienił wartości po zmianie time-zone to 'america/new_york' Gdzie DATETIME jest niezmieniony.

mysql> show variables like '%time_zone%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| system_time_zone | India Standard Time |
| time_zone        | Asia/Calcutta       |
+------------------+---------------------+

mysql> create table datedemo(
    -> mydatetime datetime,
    -> mytimestamp timestamp
    -> );

mysql> insert into datedemo values ((now()),(now()));

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 14:11:09 |
+---------------------+---------------------+

mysql> set time_zone="america/new_york";

mysql> select * from datedemo;
+---------------------+---------------------+
| mydatetime          | mytimestamp         |
+---------------------+---------------------+
| 2011-08-21 14:11:09 | 2011-08-21 04:41:09 |
+---------------------+---------------------+

Przekonwertowałem swoją odpowiedź na artykuł, aby więcej osób mogło uznać to za przydatne, MySQL: Datetime Versus Timestamp Data Types.

Główna różnica polega na tym, że DATETIME jest stała, podczas gdy znacznik czasu ma wpływ na ustawienie time_zone.

Więc ma to znaczenie tylko wtedy , gdy masz - lub może w przyszłości mieć-zsynchronizowane klastry w różnych strefach czasowych.

W prostszych słowach: Jeśli mam bazę danych w Australii i zrobię zrzut tej bazy danych, aby zsynchronizować / wypełnić bazę danych w Ameryce, wtedy znacznik czasu zaktualizuje się, aby odzwierciedlić rzeczywisty czas zdarzenia w nowej strefie czasowej, podczas gdy DATETIME nadal będzie odzwierciedlać czas Wydarzenia w strefie czasowej au.

Doskonałym przykładem używanej DATETIME, gdzie powinien być użyty znacznik czasu, jest Facebook, gdzie ich serwery nigdy nie są do końca pewni, co się dzieje w różnych strefach czasowych. Kiedyś miałem rozmowę, w której czas powiedział, że odpowiadam na wiadomości, zanim wiadomość została faktycznie wysłana. (To, oczywiście, może być również spowodowane złym tłumaczeniem strefy czasowej w oprogramowaniu do przesyłania wiadomości, Jeśli czasy były publikowane raczej niż zsynchronizowane.)

Podejmuję tę decyzję na podstawie semantycznej.

Używam znacznika czasu, gdy muszę nagrać (mniej więcej) stały punkt w czasie. Na przykład, gdy rekord został wstawiony do bazy danych lub gdy miało miejsce jakieś działanie użytkownika.

Używam pola datetime, gdy można dowolnie ustawić datę/czas. Na przykład, gdy użytkownik może zapisać później zmienić terminy.

TIMESTAMP to 4 bajty Vs 8 bajtów dla DATETIME.

Http://dev.mysql.com/doc/refman/5.0/en/storage-requirements.html

Ale jak powiedział scronide, ma niższy limit z roku 1970. Jest świetny na wszystko, co może się wydarzyć w przyszłości;)

1. Znacznik czasu to cztery bajty vs osiem bajtów dla DATETIME.

2. Znaczniki czasu są również lżejsze w bazie danych i szybciej indeksowane.

3. Typ DATETIME jest używany, gdy potrzebne są wartości zawierające zarówno informacje o dacie, jak i czasie. MySQL pobiera i wyświetla wartości DATETIME w formacie 'YYYY-MM-DD HH: MM: SS'. Obsługiwany zakres to "1000-01-01 00: 00: 00" do "9999-12-31 23: 59: 59".

Typ danych TIMESTAMP ma zakres '1970-01-01 00:00:01' UTC do '2038-01-09 03: 14: 07' UTC. Ma różne właściwości, w zależności od wersji MySQL i trybu SQL, w którym serwer jest uruchomiony.

1. DATETIME jest stała, podczas gdy znacznik czasu jest dokonywany przez ustawienie time_zone.

Zalecam użycie ani pola DATETIME ani TIMESTAMP. Jeśli chcesz reprezentować konkretny dzień jako całość( np. urodziny), użyj typu daty, ale jeśli jesteś bardziej konkretny, prawdopodobnie jesteś zainteresowany zarejestrowaniem rzeczywistej chwili w przeciwieństwie do jednostki czasu (Dzień,Tydzień,Miesiąc,Rok). Zamiast używać DATETIME lub TIMESTAMP, użyj BIGINT i po prostu zapisać liczbę milisekund od epoki (System.currentTimeMillis () jeśli używasz Javy). To ma kilka zalet:

1. unikasz blokady sprzedawcy. Prawie każda baza danych obsługuje liczby całkowite w stosunkowo podobny sposób. Załóżmy, że chcesz przenieść się do innej bazy danych. Chcesz się martwić różnicami między wartościami DateTime MySQL i tym, jak Oracle je definiuje? Nawet wśród różnych wersji MySQL znaczniki czasu mają inny poziom precyzji. Dopiero niedawno MySQL obsługiwał milisekundy w znacznikach czasu.
2. Brak strefy czasowej problemy. Pojawiło się kilka wnikliwych komentarzy na temat tego, co dzieje się z strefami czasowymi z różnymi typami danych. Ale czy jest to powszechna wiedza i czy wszyscy twoi współpracownicy poświęcą czas, aby się jej nauczyć? Z drugiej strony, ciężko jest zepsuć zmianę Biginta na Javę.util.Data. Używanie BIGINT powoduje, że wiele problemów z strefami czasowymi spada na bok.
Nie martw się o zakres i precyzję. Nie musisz się martwić o to, co zostanie skrócone o przyszłe zakresy dat (Znacznik czasu idzie tylko do 2038).
3. Integracja narzędzi innych firm. Używając liczby całkowitej, jest trywialne dla narzędzi innych firm (np. EclipseLink), aby połączyć się z bazą danych. Nie każde narzędzie innej firmy będzie miało takie samo rozumienie "datetime"jak MySQL. Chcesz spróbować dowiedzieć się w Hibernate, czy powinieneś używać Javy.sql.Znacznik czasu lub java.util.Obiekt Date, jeśli używasz tych niestandardowych typów danych? Korzystanie z podstawowych typów danych sprawia, że korzystanie z narzędzi innych firm jest banalne.

Ten problem jest ściśle związany z tym, jak należy przechowywać wartość pieniężną (tj. Czy należy użyć dziesiętnego, lub typu pieniędzy bazy danych, a co najgorsze Podwójne? Wszystkie 3 z tych opcji są straszne, z wielu tych samych powodów wymienionych powyżej. Rozwiązaniem jest przechowywanie wartości pieniędzy w centach za pomocą BIGINTA, a następnie konwersja centów na dolary po wyświetleniu wartości użytkownikowi. Zadaniem bazy danych jest przechowywanie danych, a nie ich intrepretacja. Wszystkie te wyszukane typy danych, które widzisz w bazach danych (zwłaszcza Oracle), niewiele dodają i rozpoczynają drogę do uzależnienia od dostawcy.

Zależy od zastosowania, naprawdę.

Rozważ ustawienie znacznika czasu przez użytkownika na serwerze w Nowym Jorku, na spotkanie w Sanghai. Teraz, gdy użytkownik łączy się w Sanghai, uzyskuje dostęp do tego samego znacznika czasu spotkania z serwera lustrzanego w Tokio. Spotkanie odbędzie się w czasie Tokijskim, przesuniętym od pierwotnego czasu nowojorskiego.

Więc dla wartości, które reprezentują czas użytkownika, takich jak spotkanie lub harmonogram, datetime jest lepszy. Pozwala użytkownikowi kontrolować dokładną datę i żądany czas, niezależnie od ustawień serwera. Ustawiony czas to ustawiony czas, bez wpływu na strefę czasową serwera, strefę czasową użytkownika lub zmiany sposobu obliczania czasu letniego(tak, zmienia się).

Z drugiej strony, dla wartości, które reprezentują czas systemowy, takich jak transakcje płatnicze, modyfikacje tabeli lub logowanie, Zawsze używaj znaczników czasu. System nie będzie miał wpływu na przeniesienie serwera do innej strefy czasowej lub podczas porównywania serwerów w różnych strefy czasowe.

Znaczniki czasu są również lżejsze w bazie danych i szybciej indeksowane.

2016 +: radzę ustawić strefę czasową Mysql NA UTC i użyć DATETIME:

Wszelkie najnowsze frameworki front-end (Angular 1/2, react, Vue,...) może łatwo i automatycznie konwertować czas UTC na czas lokalny.

Dodatkowo:

• DATETIME może być teraz automatycznie ustawiony na bieżącą wartość czasu Jak ustawić domyślną wartość dla kolumny DateTime MySQL?
• wbrew temu, co można by pomyśleć, DATETIME jest SZYBCIEJ NIŻ ZNACZNIK CZASU, http://gpshumano.blogs.dri.pt/2009/07/06/mysql-datetime-vs-timestamp-vs-int-performance-and-benchmarking-with-myisam/
• znacznik czasu jest nadal ograniczony do 1970-2038

(chyba że prawdopodobnie zmienisz strefę czasową swoich serwerów)

Przykład z AngularJs

// back-end: format for angular within the sql query
SELECT DATE_FORMAT(my_datetime, "%Y-%m-%dT%TZ")...

// font-end Output the localised time
{{item.my_datetime | date :'medium' }}

Wszystkie lokalizowane formaty czasu dostępne tutaj: https://docs.angularjs.org/api/ng/filter/date

Pole timestamp jest szczególnym przypadkiem pola datetime. Możesz utworzyć timestamp kolumny, aby miały specjalne właściwości; można je ustawić tak, aby aktualizowały się na create i / lub update.

W" większych " terminach bazy danych, timestamp ma kilka specjalnych wyzwalaczy.

Znacznik czasu jest zawsze w UTC (tj. upłynęły sekundy od 1970-01-01, w UTC), a serwer MySQL automatycznie konwertuje go na datę / czas dla strefy czasowej serwera. W dłuższej perspektywie czasowej znacznik czasu jest dobrym rozwiązaniem, ponieważ wiesz, że Twoje dane temporalne zawsze będą w UTC. Na przykład, nie spieprzysz dat, jeśli przeniesiesz się na inny serwer lub zmienisz ustawienia strefy czasowej na serwerze.

Porównanie DATETIME, TIMESTAMP i DATE

Co to jest [.ułamek]?

• wartość DATETIME lub TIMESTAMP może zawierać końcową ułamkową sekundy dzielą się z dokładnością do mikrosekund (6 cyfr). W szczególna, dowolna część ułamkowa w wartości wstawionej do DATETIME lub kolumna znacznika czasu jest przechowywana, a nie odrzucana. Jest to oczywiście opcjonalne.

Źródła:

• Dane daty/czasu MySQL types reference
• MySQL storage Requirements reference

Warto zauważyć, że w MySQL możesz użyć czegoś zgodnie z poniższymi liniami podczas tworzenia kolumn tabeli:

on update CURRENT_TIMESTAMP

Spowoduje to aktualizację czasu w każdym przypadku, w którym zmodyfikujesz wiersz i czasami jest bardzo pomocne dla zapisanych ostatnio informacji o edycji. To działa tylko z timestamp, nie datetime jednak.

Zawsze używałem Unix timestamp podczas pracy z MySQL i PHP. Głównym powodem tego jest domyślna metoda date w PHP używa znacznika czasu jako parametru, więc nie będzie potrzeby parsowania.

Aby uzyskać bieżący znacznik czasu Uniksa w PHP, wykonaj time();
oraz w MySQL do SELECT UNIX_TIMESTAMP();.

Z mojego doświadczenia wynika, że jeśli chcesz mieć pole daty, w którym wstawianie odbywa się tylko raz i nie chcesz mieć żadnej aktualizacji ani żadnej innej akcji w tym konkretnym polu, Wybierz date time.

Na przykład rozważmy tabelę user z polem Data rejestracji . W tabeli user, jeśli chcesz znać czas ostatniego zalogowanego użytkownika, wybierz pole typu timestamp , aby pole zostało zaktualizowane.

Jeśli tworzysz tabelę z phpMyAdmin ustawienie domyślne zaktualizuje pole timestamp , gdy nastąpi aktualizacja wiersza. Jeśli twój znacznik czasu nie jest aktualizowany za pomocą funkcji row update, możesz użyć następującego zapytania, aby pole znacznik czasu zostało automatycznie zaktualizowane.

ALTER TABLE your_table
      MODIFY COLUMN ts_activity TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;

Typ danych timestamp przechowuje datę i czas, ale w formacie UTC, a nie w bieżącym formacie strefy czasowej, jak robi to datetime. Po pobraniu danych znacznik czasu ponownie przekształca go w czas bieżącej strefy czasowej.

Załóżmy więc, że jesteś w USA i pobierasz dane z serwera, który ma strefę czasową USA. Następnie otrzymasz datę i godzinę zgodnie ze strefą czasową USA. Kolumna timestamp data type jest zawsze aktualizowana automatycznie, gdy jej wiersz zostanie zaktualizowany. Więc może być przydatne do śledzenia kiedy dany wiersz był ostatnio aktualizowany.

Po więcej szczegółów możesz przeczytać wpis na bloguTimestamp Vs Datetime .

Zawsze używam uniksowego znacznika czasu, po prostu dla zachowania rozsądku, gdy mamy do czynienia z dużą ilością informacji o datetime, szczególnie podczas wykonywania korekt dla stref czasowych, dodawania / odejmowania dat i tym podobnych. Porównując znaczniki czasu, wyklucza to czynniki komplikujące strefę czasową i pozwala oszczędzić zasoby w przetwarzaniu po stronie serwera (niezależnie od tego, czy jest to kod aplikacji, czy zapytania do bazy danych) , ponieważ używasz lekkiej arytmetyki, a nie cięższej dodawania/odejmowania daty i czasu funkcje.

Kolejna rzecz warta rozważenia:

Jeśli budujesz aplikację, nigdy nie wiesz, w jaki sposób Twoje dane mogą być używane w dół linii. Jeśli będziesz musiał, powiedzmy, porównać kilka rekordów w swoim zestawie danych, z, powiedzmy, kilkoma elementami z interfejsu API innych firm, i powiedzmy, umieścić je w porządku chronologicznym, będziesz zadowolony z uniksowych znaczników czasu dla swoich wierszy. Nawet jeśli zdecydujesz się użyć znaczników czasu MySQL, przechowuj znacznik czasu Uniksa jako ubezpieczenie.

Główna różnica to

• indeks na Timestamp-działa
• indeks jest na Datetime - nie działa

Spójrz na ten post aby zobaczyć problemy z indeksowaniem Datetime

Uważaj na zmianę znacznika czasu podczas wykonywania instrukcji aktualizacji na stole. Jeśli masz tabelę z kolumnami "Name" (varchar), " Age "(int) i "Date_Added" (timestamp) i uruchamiasz następującą instrukcję DML

UPDATE table
SET age = 30

Wtedy każda pojedyncza wartość w kolumnie 'Date_Added' zostanie zmieniona na bieżący znacznik czasu.

Inną różnicą między znacznikiem czasu a Datetime jest Znacznik Czasu, którego nie można ustawić na wartość NULL.

Znalazłem niezrównaną użyteczność w możliwości znacznika czasu do automatycznej aktualizacji się na podstawie bieżącego czasu bez użycia niepotrzebnych wyzwalaczy. To tylko ja, chociaż znacznik czasu jest UTC, jak to powiedziano.

Może śledzić w różnych strefach czasowych, więc jeśli chcesz wyświetlić względny czas, na przykład, czas UTC jest tym, czego chcesz.

Odniesienie zaczerpnięte z tego artykułu:

Główne różnice:

Znacznik czasu używany do śledzenia zmian w rekordach i aktualizacji za każdym razem, gdy rekord jest zmieniany. DATETIME używany do przechowywania określonej i statycznej wartości, na którą nie mają wpływu żadne zmiany w rekordach.

Znacznik czasu ma również wpływ na różne ustawienia związane ze strefą czasową. DATETIME jest stała.

Znacznik czasu wewnętrznie konwertuje bieżącą strefę czasową na UTC do przechowywania i podczas pobierania konwertowane z powrotem do bieżącej strefy czasowej. DATETIME nie może tego zrobić.

Zakres obsługiwanych znaczników czasu: '1970-01-01 00: 00: 01 'UTC do' 2038-01-19 03: 14: 07 ' UTC Zakres obsługiwany przez DATETIME: "1000-01-01 00: 00: 00" to '9999-12-31 23:59:59'

Wolę używać znacznika czasu, aby zachować wszystko w jednym wspólnym formacie raw i sformatować dane w kodzie PHP lub w zapytaniu SQL. Są przypadki, w których przydaje się w kodzie, aby zachować wszystko w ciągu kilku sekund.

W moim przypadku ustawiłem UTC jako strefę czasową dla wszystkiego: systemu, serwera bazy danych itp. za każdym razem, kiedy mogę. Jeśli mój klient wymaga innej strefy czasowej, konfiguruję ją w aplikacji.

Prawie zawsze wolę znaczniki czasu niż pola datetime, ponieważ znaczniki czasu zawierają strefę czasową w sposób niejawny. Tak więc, od momentu, że aplikacja będzie dostępna dla użytkowników z różnych stref czasowych i chcesz, aby widzieli daty i godziny w ich lokalnej strefie czasowej, ten typ pola sprawia, że jest to dość łatwe, niż gdyby dane zostały zapisane w polach datetime.

Jako plus, w przypadku migracji bazy danych do systemu z inną strefą czasową, czułbym się pewniej używając znaczników czasu. Nie mówiąc o możliwych problemach przy obliczaniu różnic między dwoma momentami z sumaryczną zmianą czasu pomiędzy i wymagającą dokładności 1 godziny lub mniej.

Podsumowując, cenię sobie zalety znacznika czasu:

• gotowy do użycia na Międzynarodowym (multi time Strefa) Aplikacje
• łatwe migracje między strefami czasowymi
• dość łatwe do obliczenia różnice (wystarczy odjąć oba znaczniki czasu)
• nie martw się o daty w / poza okresem letnim

Z tych wszystkich powodów wybieram pola UTC i timestamp, gdzie są dostępne. I unikam bólów głowy;)

Lubię Unix timestamp, ponieważ można przekonwertować na Liczby i po prostu martwić się o liczbę. Plus dodajesz/odejmujesz i dostajesz czas trwania itp. Następnie przekonwertuj wynik na datę w dowolnym formacie. Ten kod dowiaduje się, ile czasu w minutach minęło między znacznikiem czasu z dokumentu, a bieżącym czasem.

$date  = $item['pubdate']; (etc ...)
$unix_now = time();
$result = strtotime($date, $unix_now);
$unix_diff_min = (($unix_now  - $result) / 60);
$min = round($unix_diff_min);

A TIMESTAMP wymaga 4 bajtów, podczas gdy a DATETIME wymaga 8 bajtów.

Znacznik czasu jest przydatny, gdy masz gości z różnych krajów o różnych strefach czasowych. możesz łatwo przekonwertować znacznik czasu na dowolną strefę czasową kraju

Używam tylko unsigned BIGINT podczas przechowywania UTC ...

, które następnie nadal można dostosować do czasu lokalnego w PHP.

The DATETIME to be selected with FROM_UNIXTIME( integer_timestamp_column ).

Należy oczywiście ustawić indeks na tej kolumnie, w przeciwnym razie nie byłoby zaliczki.