Sollte ich in MySQL den Datentyp datetime oder timestamp verwenden?

Question

Würden Sie die Verwendung eines datetime oder eine Zeitstempel Feld und warum (unter Verwendung von MySQL)?

Ich arbeite mit PHP auf der Serverseite.

Answer 1

Ich treffe diese Entscheidung auf einer semantischen Basis.

Ich verwende einen Zeitstempel, wenn ich einen (mehr oder weniger) festen Zeitpunkt aufzeichnen muss. Zum Beispiel, wann ein Datensatz in die Datenbank eingefügt wurde oder wann eine Benutzeraktion stattgefunden hat.

Ich verwende ein Datetime-Feld, wenn das Datum/die Uhrzeit beliebig eingestellt und geändert werden kann. Zum Beispiel, wenn ein Benutzer Termine speichern und später ändern kann.

Answer 2

1. TIMESTAMP beträgt vier Bytes gegenüber acht Bytes bei DATETIME .

2. Zeitstempel belasten auch die Datenbank weniger und werden schneller indiziert.

3. El DATETIME wird verwendet, wenn Sie Werte benötigen, die sowohl Datums- als auch Zeitinformationen enthalten. MySQL ruft ab und zeigt an DATETIME Werte in YYYY-MM-DD HH:MM:SS Format. Der unterstützte Bereich ist 1000-01-01 00:00:00 まで 9999-12-31 23:59:59 . Die TIMESTAMP Datentyp hat einen Bereich von 1970-01-01 00:00:01 UTC まで 2038-01-09 03:14:07 UTC . Sie hat unterschiedliche Eigenschaften, die von der MySQL-Version und dem SQL-Modus abhängen, in dem der Server läuft.

4. DATETIME konstant ist, während TIMESTAMP wird beeinflusst durch den time_zone Umgebung.

Answer 3

Ich empfehle die Verwendung von weder ein DATETIME- oder ein TIMESTAMP-Feld. Wenn Sie einen bestimmten Tag als Ganzes darstellen wollen (z. B. einen Geburtstag), dann verwenden Sie den Typ DATE. Wenn Sie jedoch spezifischer sind, dann sind Sie wahrscheinlich daran interessiert, einen tatsächlichen Moment aufzuzeichnen und nicht eine Zeiteinheit (Tag, Woche, Monat, Jahr). Verwenden Sie statt DATETIME oder TIMESTAMP einen BIGINT und speichern Sie einfach die Anzahl der Millisekunden seit der Epoche (System.currentTimeMillis(), wenn Sie Java verwenden). Dies hat mehrere Vorteile:

1. Sie vermeiden die Bindung an einen bestimmten Anbieter. So gut wie jede Datenbank unterstützt Ganzzahlen auf relativ ähnliche Weise. Angenommen, Sie möchten zu einer anderen Datenbank wechseln. Wollen Sie sich um die Unterschiede zwischen den DATETIME-Werten von MySQL und deren Definition durch Oracle kümmern? Selbst zwischen verschiedenen Versionen von MySQL haben TIMESTAMPS einen unterschiedlichen Genauigkeitsgrad. Erst seit kurzem unterstützt MySQL Millisekunden in den Zeitstempeln.
2. Keine Zeitzonenprobleme. Es gab hier einige aufschlussreiche Kommentare darüber, was mit Zeitzonen bei den verschiedenen Datentypen passiert. Aber ist das allgemein bekannt, und werden sich Ihre Mitarbeiter die Zeit nehmen, das zu lernen? Andererseits ist es ziemlich schwierig, einen BigINT in ein java.util.Date umzuwandeln. Bei der Verwendung eines BIGINT bleiben viele Probleme mit Zeitzonen auf der Strecke.
3. Sie müssen sich keine Gedanken über Reichweiten oder Präzision machen. Sie müssen sich keine Gedanken darüber machen, was durch zukünftige Datumsbereiche verkürzt wird (TIMESTAMP geht nur bis 2038).
4. Integration von Drittanbieter-Tools. Durch die Verwendung einer Ganzzahl ist es für Tools von Drittanbietern (z. B. EclipseLink) trivial, eine Schnittstelle zur Datenbank herzustellen. Nicht jedes Werkzeug eines Drittanbieters hat das gleiche Verständnis von "datetime" wie MySQL. Möchten Sie versuchen, in Hibernate herauszufinden, ob Sie ein java.sql.TimeStamp- oder java.util.Date-Objekt verwenden sollten, wenn Sie diese benutzerdefinierten Datentypen verwenden? Die Verwendung Ihrer Basisdatentypen macht die Verwendung mit Tools von Drittanbietern trivial.

Diese Frage hängt eng damit zusammen, wie Sie einen Geldwert (z.B. $1,99) in einer Datenbank speichern sollten. Sollten Sie einen Dezimalwert, den Geldtyp der Datenbank oder am schlimmsten ein Double verwenden? Alle 3 Optionen sind aus vielen der oben genannten Gründe schlecht. Die Lösung besteht darin, den Geldwert mit BIGINT in Cents zu speichern und dann Cents in Dollar umzuwandeln, wenn Sie den Wert für den Benutzer anzeigen. Die Aufgabe der Datenbank ist es, Daten zu speichern, und NICHT, diese Daten zu interpretieren. All diese ausgefallenen Datentypen, die man in Datenbanken (vor allem in Oracle) sieht, bringen wenig und führen dazu, dass man sich an einen Anbieter bindet.

Answer 4

TIMESTAMP ist 4 Byte groß, gegenüber 8 Byte bei DATETIME.

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

Aber wie scronide sagte, gibt es eine untere Grenze für das Jahr 1970. Es ist jedoch großartig für alles, was in der Zukunft passieren könnte ;)

Answer 5

Weder noch. Die DATETIME y TIMESTAMP Typen sind für allgemeine Anwendungsfälle grundsätzlich nicht geeignet. MySQL wird sie in Zukunft ändern. Sie sollten BIGINT und UNIX-Zeitstempel, es sei denn, Sie haben einen besonderen Grund, etwas anderes zu verwenden.

Besondere Fälle

Hier sind einige spezifische Situationen, in denen Ihre Wahl einfacher ist und Sie die Analyse und allgemeine Empfehlung in dieser Antwort nicht benötigen.

• Nur Datum - wenn Sie sich nur für das Datum interessieren (z. B. das Datum des nächsten Mondneujahrs, 2022-02-01) UND Ihnen klar ist, in welche Zeitzone dieses Datum fällt (oder es Ihnen egal ist, wie im Fall des Mondneujahrs), dann verwenden Sie die DATE Spaltentyp.

• Einfügezeiten aufzeichnen - wenn Sie die Einfügedaten/-zeiten für Zeilen in Ihrer Datenbank protokollieren UND es Ihnen egal ist, dass Ihre Anwendung in den nächsten 17 Jahren brechen wird dann verwenden Sie einfach TIMESTAMP mit einem Standardwert von CURRENT_TIMESTAMP() .

Warum ist TIMESTAMP kaputt?

El TIMESTAMP Typ wird auf der Festplatte in der Zeitzone UTC gespeichert. Das bedeutet, dass Ihr Server nicht kaputt geht, wenn Sie ihn physisch bewegen. Das ist gut. Aber Zeitstempel, wie sie derzeit definiert sind, werden im Jahr 2038 nicht mehr funktionieren.

Jedes Mal, wenn Sie INSERT INTO o SELECT FROM a TIMESTAMP Spalte wird der physische Standort (d. h. die Zeitzonenkonfiguration) Ihres Clients/Anwendungsservers berücksichtigt. Wenn Sie Ihren Anwendungsserver verlegen, ändern sich Ihre Daten.

Warum ist VARCHAR kaputt?

El VARCHAR ermöglicht die eindeutige Speicherung eines nicht-lokalen Datums/einer nicht-lokalen Uhrzeit/beides im ISO 8601-Format und funktioniert für Daten nach 2037. Es ist üblich, die Zulu-Zeit zu verwenden, aber ISO 8601 erlaubt die Kodierung eines beliebigen Offsets. Dies ist weniger nützlich, da die MySQL-Datums- und Zeitfunktionen zwar Zeichenketten als Eingabe überall dort unterstützen, wo Datum/Uhrzeit/beides erwartet wird, das Ergebnis aber falsch ist, wenn die Eingabe Zeitzonen-Offsets verwendet.

Auch VARCHAR verwendet zusätzliche Bytes an Speicherplatz.

Warum ist DATETIME kaputt?

A DATETIME speichert eine DATE und eine TIME in derselben Spalte. Beides hat keine Bedeutung, wenn die Zeitzone nicht bekannt ist, und die Zeitzone ist nirgendwo gespeichert. Sie sollten die gewünschte Zeitzone als Kommentar in die Spalte einfügen, da die Zeitzone untrennbar mit den Daten verbunden ist. Nur wenige Leute verwenden Spaltenkommentare, daher ist dies ein Fehler, der nur darauf wartet zu passieren. Ich habe einen Server aus Arizona geerbt und muss daher immer alle Zeitstempel VON der Zeit in Arizona und dann in eine andere Zeit umwandeln.

(Update 2021-12-08 Ich habe den Server nach Jahren der Betriebszeit neu gestartet und der Datenbank-Client (mit Upgrades) wurde auf UTC zurückgesetzt. Das bedeutet, dass meine Anwendung Daten vor und nach dem Zurücksetzen unterschiedlich behandeln muss. Hardcode!)

Die einzige Situation, in der ein DATETIME richtig ist, diesen Satz zu vervollständigen:

Ihr neues Sonnenjahr 2020 beginnt genau um DATETIME("2020-01-01 00:00:00") .

Es gibt keine andere gute Verwendung für DATETIME s. Vielleicht stellen Sie sich einen Webserver für eine Stadtverwaltung in Delaware vor. Die Zeitzone für diesen Server und alle Personen, die auf diesen Server zugreifen, liegt doch sicher in Delaware, in der Eastern Time Zone, oder? Falsch! In diesem Jahrtausend denken wir alle, dass Server in der "Wolke" existieren. Es ist also immer falsch, Ihren Server in einer bestimmten Zeitzone anzusiedeln, denn Ihr Server wird eines Tages verlegt werden.

Hinweis: MySQL ist jetzt unterstützt Zeitzonenverschiebungen en DATETIME Literale ( danke @Marko ). Dies kann das Einfügen von DATETIME Das ist zwar bequemer für Sie, aber die unvollständige und daher unbrauchbare Bedeutung der Daten, die dieses fatale Problem kennzeichnet (""), wird dadurch nicht behoben.

Wie zu verwenden BIGINT ?

Definieren Sie:

CREATE TEMPORARY TABLE good_times (
    a_time BIGINT
)

Einen bestimmten Wert einfügen:

INSERT INTO good_times VALUES (
    UNIX_TIMESTAMP(CONVERT_TZ("2014-12-03 12:24:54", '+00:00', @@global.time_zone))
);

Oder natürlich ist dies viel besser von Ihrer Anwendung, wie:

$statement = $myDB->prepare('INSERT INTO good_times VALUES (?)');
$statement->execute([$someTime->getTimestamp()]);

Wählen Sie aus:

SELECT a_time FROM good_times;

Es gibt Techniken zum Filtern relativer Zeiten (Auswahl von Beiträgen innerhalb der letzten 30 Tage, Suche nach Nutzern, die innerhalb von 10 Minuten nach der Registrierung gekauft haben), die den Rahmen dieses Artikels sprengen würden.