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


Czy poleciłbyś używać a.? datetime lub znak czasu pole i dlaczego (przy użyciu MySQL)?

Pracuję z PHP po stronie serwera.


2304
2018-01-03 16:14


pochodzenie


to ma pewne istotne informacje codeproject.com/Tips/1215635/MySQL-DATETIME-vs-TIMESTAMP - Waqleh


Odpowiedzi:


Znaczniki czasu w MySQL są zwykle używane do śledzenia zmian w rekordach i często są aktualizowane za każdym razem, gdy rekord jest zmieniany. Jeśli chcesz zapisać określoną wartość, powinieneś użyć pola datetime.

Jeśli chcesz zdecydować, czy chcesz użyć znacznika czasu UNIX, czy natywnego pola datetime MySQL, przejdź do formatu macierzystego. W ten sposób możesz wykonywać obliczenia w MySQL ("SELECT DATE_ADD(my_datetime, INTERVAL 1 DAY)") i łatwo jest zmienić format wartości na znacznik czasu UNIX ("SELECT UNIX_TIMESTAMP(my_datetime)") kiedy wyszukujesz rekord, jeśli chcesz operować na nim za pomocą PHP.


1559
2018-01-03 16:26



Ważna różnica polega na tym DATETIME reprezentuje datę (jak można znaleźć w kalendarzu) i czas (jak można zaobserwować na zegarze ściennym), natomiast TIMESTAMP reprezentuje dobrze zdefiniowany punkt w czasie. Może to być bardzo ważne, jeśli aplikacja obsługuje strefy czasowe. Jak dawno temu był "2010-09-01 16:31:00"? To zależy od tego, w której strefie czasowej jesteś. Dla mnie to było zaledwie kilka sekund temu, dla ciebie może to być czas w przyszłości. Jeśli powiem 1283351460 sekund od "1970-01-01 00:00:00 UTC", wiesz dokładnie, o której godzinie rozmawiam. (Patrz doskonała odpowiedź Nira poniżej). [Wada: prawidłowy zakres]. - MattBianco
Kolejna różnica: zapytania z "natywną" datetime nie będą buforowane, ale zapytania z timestamp - będą. - OZ_
"Znaczniki czasu w MySQL zwykle używane do śledzenia zmian w rekordach" Nie sądzę, że to dobra odpowiedź. Znacznik czasu jest znacznie potężniejszy i bardziej skomplikowany niż w przypadku MattBianco i Nir. Chociaż druga część odpowiedzi jest bardzo dobra. To prawda, co powiedział bąbelek i jest to dobra rada. - santiagobasulto
Również 1 ważna notatka, DATETIME i TIMESTAMP mogą używać CURRENT_TIMESTAMP, NOW () z szacunkiem, ponieważ jest to wartość domyślna, ale DATE na przykład nie może, ponieważ domyślnie używa "0000-00-00", więc aby rozwiązać tę kwestię, U powinno pisać Twój własny wyzwalacz do tej tabeli, aby wstawić aktualną datę (bez czasu) w polu / col z typem DYS mysql. - Arthur Kushman
@DavidHarkness: Dokumentacja mówi "Aby określić właściwości automatyczne, użyj klauzul DEFAULT CURRENT_TIMESTAMP i ON UPDATE CURRENT_TIMESTAMP" dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html - Plap


W MySQL 5 i nowszych, ZNAK CZASU wartości są konwertowane z bieżącej strefy czasowej na UTC w celu przechowywania i konwertowane z powrotem z UTC do bieżącej strefy czasowej do pobierania. (Ma to miejsce tylko dla typu danych TIMESTAMP, oraz nie dla innych typów, takich jak DATETIME.)

Domyślnie bieżąca strefa czasowa dla każdego połączenia jest czasem serwera. Strefę czasową można ustawić dla każdego połączenia, zgodnie z opisem w Obsługa strefy czasowej serwera MySQL.


816
2018-03-02 11:49



Źródło: dev.mysql.com/doc/refman/5.1/en/datetime.html - Andy0708
ta prezentacja OReilly jest bardzo dobra dla tego tematu (PDF sorry) cdn.oreillystatic.com/en/assets/1/event/36/... - gcb
Dotyczy to również charakteru imprezy: - wideokonferencja (TIMESTAMP). Wszyscy uczestnicy powinni zobaczyć odniesienie do absolutnej chwili dostosowanej do strefy czasowej. - Lokalny czas zadania (DATETIME), powinienem wykonać to zadanie w dniu 2014/03/31 9:00 Nie ma znaczenia, czy tego dnia pracuję w Nowym Jorku lub Paryżu. Zacznę pracować o 8:00 czasu lokalnego, bo będę tego dnia. - yucer
Więc jeśli ZMIENIĘ strefę czasową serwera, to wartość TIMESTAMP pozostanie taka sama lub też się zmieni? - Andrew
@Andrew, ponieważ jest to UTC, pozostanie UTC. Konwersja wsteczna zmieni się jednak na strefę czasową "nowego" serwera. - nembleton


Zawsze używam pól DATETIME dla niczego innego niż metadane wiersza (data utworzenia lub modyfikacji).

Tak jak wzmiankowany w dokumentacji MySQL:

Typ DATETIME jest używany, gdy potrzebujesz wartości zawierających informacje o dacie i godzinie. MySQL wyszukuje i wyświetla wartości DATETIME w formacie "RRRR-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 zasięg "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 działa serwer.

Prawdopodobnie osiągniesz niższy limit TIMESTAMPów w ogólnym użyciu - np. przechowywanie daty urodzin.


430
2018-01-04 04:26



możesz także trafić w górny ograniczaj się łatwo, jeśli jesteś w bankowości lub nieruchomości ... 30-letnie kredyty hipoteczne przekraczają 2038 rok - Kip
Oczywiście użyj 64-bitowych znaczników czasu Unix. Na przykład w Javie, new Date().getTime() już daje 64-bitową wartość. - osa
+1 dla DATETIME: W naszym przepływie danych, z SQL Server sklepu fizycznego do zakupu i faktury, przesłany do serwera MySQL dla Sklepu wirtualnego na stronie, DATETIME jest lepszy dla modyfikacji Data-Godzina, ponieważ ten typ danych istnieje również w Transact -SQL. Ale TIMESTAMP oznacza coś innego w języku Transact-SQL, jest Binarny jak ROWVERSION, chociaż MySQL TIMESTAMP jest podobny do DateTime. Dlatego unikamy użycia TIMESTAMP dla kompatybilności dwóch DB. - jacouh
Brak pomysłu. Jest to znacznie większy problem niż tylko MySQL i nie ma prostej poprawki: en.wikipedia.org/wiki/Year_2038_problem Nie wierzę, że MySQL może po prostu zadeklarować znaczniki czasu, które są teraz 64-bitowe i zakładać, że wszystko będzie dobrze. Nie kontrolują sprzętu. - scronide
Data urodzenia może być DATETIME, jeśli znasz godzinę urodzenia, tak jak ja dla mojej. - Kris Craig


Poniższe przykłady pokazują, w jaki sposób TIMESTAMP typ daty zmienił wartości po zmianie time-zone to 'america/new_york' gdzie DATETIMEpozostaje 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 |
+---------------------+---------------------+

Przekształciłem swoją odpowiedź w artykuł, aby więcej osób mogło znaleźć to przydatne, MySQL: Datetime Versus Datastamp Typy danych.


284
2017-08-21 08:45



Cóż, właściwie to DATETIME efektywny czas zmieniony wraz ze zmianą strefy czasowej, TIMESTAMP nie zmieniło się, ale ludzka reprezentacja zrobiła. - flindeberg
Wygląda na to, że to polecenie nie działa w MySQL 5.6 set time_zone="america/new_york"; - Manjunath Reddy
Oto odpowiedź na ustawiony problem time_zone. stackoverflow.com/questions/3451847/mysql-timezone-change - Manjunath Reddy
Zgadzam się z @flindeberg. Znacznik czasu reprezentuje prawidłowy czas, a nie datetime, po zmianie strefy czasowej - Nitin Bansal


Główną różnicą jest to, że DATETIME jest stały, podczas gdy TIMESTAMP ma wpływ na time_zone oprawa.

Ma więc znaczenie tylko wtedy, gdy masz - lub możesz w przyszłości - zsynchronizować klastry w różnych strefach czasowych.

W prostszych słowach: Jeśli mam bazę danych w Australii i wykonuję zrzut tej bazy danych w celu zsynchronizowania / zapełnienia bazy danych w Ameryce, wówczas TIMESTAMP zaktualizuje się, aby odzwierciedlić rzeczywisty czas zdarzenia w nowej strefie czasowej, natomiast DATETIME nadal będzie odzwierciedlał czas zdarzenia w strefie czasowej au.

Doskonałym przykładem użycia DATETIME, w którym TIMESTAMP powinien był zostać użyty, jest Facebook, gdzie ich serwery nigdy nie są do końca pewne, co wydarzyło się 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 mogło być również spowodowane złym przekształceniem strefy czasowej w oprogramowaniu do przesyłania wiadomości, jeśli czasy były wysyłane, a nie synchronizowane.)


169
2018-01-14 14:26



Nie sądzę, że to dobry sposób myślenia. Chciałbym tylko przechowywać i przetwarzać wszystkie daty w UTC i upewnić się, że front-end wyświetla go zgodnie z daną strefą czasową. Takie podejście jest proste i przewidywalne. - Kos
@Kos: nie zapisuje i przetwarza wszystkich dat w UTC dokładnie to, co TIMESTAMP robi wewnętrznie? (Następnie konwertujesz, aby wyświetlić lokalną strefę czasową?) - carbocation
mój lokalna strefa czasowa? W jaki sposób twój DB znał moją strefę czasową? ;-) Zarówno baza danych, jak i interfejs użytkownika są zwykle nieco przetwarzane. Robię lokalizację tylko po całym przetwarzaniu. - Kos
@Koz: moja baza danych nie zna strefy czasowej baz danych:! Ale zna znacznik czasu. Twoja baza danych zna własne ustawienia strefy czasowej i stosuje je podczas interpretowania / reprezentowania znacznika czasu. 1:01 11 grudnia 2013 r. W Pekinie Chiny nie są tym samym momentem czasu, co 1:01 w dniu 11 grudnia 2013 r. W Sydney w Australii. Google: "strefy czasowe" i "główny południk". - ekerner
Różnica polega nie tylko na strefach czasowych innych serwerów klastra. Odpowiednim ustawieniem jest zdefiniowana strefa czasowa (domyślnie to samo serwera, ale domyślna wartość może zależeć od sterownika po stronie klienta) w połączeniu z dowolnego klienta MySQL. - dolmen


Podejmuję tę decyzję na bazie semantycznej.

Używam znacznika czasu, kiedy muszę nagrać (mniej więcej) ustalony punkt w czasie. Na przykład, gdy rekord został wstawiony do bazy danych lub gdy nastąpiło działanie użytkownika.

Używam pola datetime, gdy data / czas mogą być ustawione i zmienione arbitralnie. Na przykład, gdy użytkownik może zapisać później zmiany terminów.


109
2018-01-03 17:11



timestamp - ustalony punkt w czasie, Coordinated Universal Time - w odniesieniu do punktu widzenia, do czasu odniesienia (czas lokalny strefy czasowej strefy czasowej), może być .. Skoordynowany czas lokalny? - yucer


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

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

Ale jak w przypadku scronide, ma on niższy limit z 1970 roku. Jest to świetne rozwiązanie dla wszystkiego, co może się wydarzyć w przyszłości;)


86
2018-01-04 09:00



Przyszłość kończy się o 2038-01-19 03:14:07 UTC. - MattBianco
To głupie. Myślałem, że typ TIMESTAMP będzie "gotowy na przyszłość", ponieważ jest stosunkowo nowy. Nie możesz zawracać sobie głowy konwertowaniem datetime na UTC i wstecz za każdym razem, gdy pracujesz w różnych strefach czasowych. Powinni byli zwiększyć TIMESTAMP. Co najmniej 1 bajt większy. doda 68 * 255 = 17340 więcej lat ... chociaż nie będzie wyrównany do 32 bitów. - NickSoft
Czy wiesz, dlaczego TIMESTAMP kończy się w 2038 roku? Ponieważ jest to liczba całkowita, a liczby całkowite mają limit 2147483647. Myślę, że to jest powód. Być może, jeśli zmienią swój typ na varchar i zmienią sposób manipulowania nim, będzie on "gotowy na przyszłość". - vinsa
@ vinsa, nie sądzę, że do tego czasu będzie gotowa na przyszłość. Wciąż pamiętacie błąd Y2K? Nadchodzi 2038. - Pacerier
Do roku 2038 MySQL (jeśli nadal istnieje) po prostu zaktualizuje pole TIMESTAMP, aby użyć więcej niż 4 bajty i pozwoli na szerszy zakres - the_nuts


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

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

  3. Typ DATETIME jest używany, gdy potrzebujesz wartości zawierających informacje o dacie i godzinie. MySQL wyszukuje i wyświetla wartości DATETIME w formacie "RRRR-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 zasięg "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 działa serwer.

  1. DATETIME jest stały, podczas gdy TIMESTAMP ma wpływ na ustawienie time_zone.

80
2017-12-21 11:12



Musisz wyjaśnić # 4: Znacznik czasu jako przechowywany jest stały i nie-względny (całkowicie agnostyczny) do strefy czasowej, podczas gdy wyświetlanie wyjścia przy pobieraniu zależy od strefy czasowej sesji wysyłającej żądanie. DATETIME odnosi się zawsze do strefy czasowej, w której została zapisana, i zawsze musi być brane pod uwagę w tym kontekście, a odpowiedzialność spoczywa teraz na aplikacji. - Christopher McGowan
Świetna odpowiedź. Aby dodać do tej odpowiedzi, jeśli spróbujemy zapisać wartości wykraczające poza "2038-01-09 03:14:07", albo otrzymamy błąd, albo zostanie on zapisany jako "0000-00-00 00:00:00". Otrzymałem ten błąd dla mojej bazy danych, ponieważ ma przesunięte czasy wartości (dla celów szyfrowania). - Earnest_learner


Polecam używanie ani pole DATETIME lub TIMESTAMP. Jeśli chcesz reprezentować konkretny dzień jako całość (np. Urodziny), to użyj typu DATE, ale jeśli jesteś bardziej konkretny, prawdopodobnie chcesz nagrać rzeczywisty moment w przeciwieństwie do jednostki czas (dzień, tydzień, miesiąc, rok). Zamiast używać DATETIME lub TIMESTAMP, użyj BIGINT i po prostu przechowuj liczbę milisekund od epoki (System.currentTimeMillis (), jeśli używasz Java). Ma to kilka zalet:

  1. Unikasz zablokowania dostawcy. Niemal każda baza danych obsługuje liczby całkowite w stosunkowo podobny sposób. Załóżmy, że chcesz przenieść się do innej bazy danych. Czy chcesz się martwić różnicami między wartościami DATETIME MySQL i tym, w jaki sposób Oracle je definiuje? Nawet wśród różnych wersji MySQL, TIMESTAMPS mają inny poziom precyzji. Dopiero niedawno MySQL obsłużył milisekundy w sygnaturach czasowych.
  2. Brak problemów z strefą czasową. Było trochę wnikliwych komentarzy na temat tego, co dzieje się z strefami czasowymi z różnymi typami danych. Ale czy jest to powszechna wiedza i czy twoi współpracownicy wszyscy poświęcą czas, aby się jej nauczyć? Z drugiej strony, trudno jest zepsuć zmianę BigINT na java.util.Date. Używanie BIGINT powoduje wiele problemów z strefami czasowymi.
  3. Bez obaw o zakresy i precyzję. Nie musisz się martwić o to, co zostało skrócone o przyszłe zakresy dat (TIMESTAMP dotyczy tylko 2038).
  4. Zewnętrzna integracja narzędzi. Używając liczby całkowitej, łatwo jest połączyć się z bazą danych w narzędziach innych firm (na przykład EclipseLink). Nie każde narzędzie innej firmy będzie miało takie samo zrozumienie "datetime" jak MySQL. Czy chcesz spróbować dowiedzieć się w Hibernate, czy powinieneś użyć obiektu java.sql.TimeStamp lub java.util.Date, jeśli używasz tych niestandardowych typów danych? Korzystając z podstawowych typów danych, używaj prostych narzędzi zewnętrznych.

Ten problem jest ściśle związany z tym, jak należy przechowywać w bazie danych wartość pieniężną (tj. 1,99 USD). Czy należy użyć wartości dziesiętnej lub typu pieniężnego bazy danych, czy najgorszego? Wszystkie trzy opcje są okropne, z wielu z tych samych powodów wymienionych powyżej. Rozwiązaniem jest przechowywanie wartości pieniędzy w centach za pomocą metody BIGINT, a następnie przeliczanie centów na dolary, gdy wyświetlasz wartość użytkownikowi. Zadaniem bazy danych jest przechowywanie danych, a NIE nieprzebywanie tych danych. Wszystkie te fantazyjne typy danych, które widzisz w bazach danych (szczególnie w Oracle), niewiele dodają i zaczynają na drodze do blokady dostawcy.


75
2018-06-11 23:02



Podoba mi się to rozwiązanie. TIMESTAMP wygasający w 2038 roku jest poważnym problemem. To naprawdę nie jest tak daleko! - Charlie Dalsass
@CharlieDalsass Czy uważasz, że obecne oprogramowanie będzie tam, że czas :-P - Habeeb Perwad
Jestem w kłopoty z TIMESTAMP. Ta alternatywa BIGINT wydaje się być lepsza. - KcFnMi
Utrudnia wyszukiwanie danych według daty, jeśli jest przechowywany jako liczba milisekund - Ali Saeed
To naprawdę najlepsze rozwiązanie, jeśli zależy Ci na przenośności i obsłudze użytkowników w wielu strefach czasowych lub na bazie danych w innej strefie czasowej niż użytkownicy. TIMESTAMP może być rozwiązaniem, jeśli nie ma wad projektowych. Szkoda, że ​​zepsute rozwiązania uzyskały więcej głosów, ponieważ zostały wysłane wcześnie (lata!). - German


Zależy od aplikacji, naprawdę.

Rozważ ustawienie 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. Obejrzy spotkanie w Tokio, z odsunięciem od pierwotnego czasu w Nowym Jorku.

Więc dla wartości reprezentujących czas użytkownika, takich jak spotkanie lub harmonogram, datetime jest lepszy. Pozwala użytkownikowi kontrolować dokładną datę i czas, niezależnie od ustawień serwera. Ustawionym czasem jest ustawiony czas, na który nie wpływa strefa czasowa serwera, strefa czasowa użytkownika lub zmiany sposobu obliczania czasu letniego (tak, to się zmienia).

Z drugiej strony, dla wartości reprezentujących czas systemowy, takich jak transakcje płatnicze, modyfikacje tabel lub rejestrowanie, zawsze używaj znaczników czasowych. Nie wpłynie to na system, przenosząc serwer do innej strefy czasowej lub porównując serwery w różnych strefach czasowych.

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


37
2017-10-26 21:07