Verwenden Sie die aktuelle UTC-Zeit als Standard in PostgreSQL

1. Standardmethode zum Abrufen der UTC-Zeit in PostgreSQL
2. Erstellen Sie Tabellen mit der Spalte TIMESTAMP im UTC-Format
3. Verwenden Sie TIMEZONE(), um TIME im UTC-Format für die Tabellenspalte in PostgreSQL zu erhalten
4. Verpacken Sie das Zeitformat UTC für eine bessere Effizienz in eine Funktion

Heute lernen wir in PostgreSQL, wie wir die aktuelle Zeit UTC als Standardzeit in unseren Tabellen in PostgreSQL verwenden. UTC steht für COORDINATED UNIVERSAL TIME.

Es ist die UNIVERSELLE Methode zur Regulierung von Uhren und Zeit weltweit. Lokale Zeitzonen haben nur Zeitangaben für einen bestimmten Ort und werden hauptsächlich verwendet, um Bürger besser zu verstehen.

Wir werden uns die verschiedenen Lösungen ansehen, um die aktuelle Zeit in unserem PostgreSQL-Server als UTC einzustellen.

Standardmethode zum Abrufen der UTC-Zeit in PostgreSQL

Wir können eine einfache Abfrage aufrufen, um die Zeit im UTC-Format für unsere aktuelle PostgreSQL-Sitzung zu erhalten.

select now() at time zone ('utc');

Dies gibt uns eine Tabelle wie folgt zurück.

Ausgang:

timezone (timestamp without timezone)
"2022-04-27 16:38:29.354155"

Wie funktioniert das? Zuerst rufen wir NOW() auf, um die Startzeit der aktuellen Transaktion zu erhalten.

Diese ergibt sich aus der im Format UTC definierten TIME ZONE. Diese einfache Abfrage hilft uns also, die aktuelle Zeit in der von uns benötigten Zone zu erhalten.

Wenn Sie dazu neigen, diese Abfrage aufzurufen, ohne die Klausel TIMEZONE zu definieren, werden Sie feststellen, dass Ihre Sitzung einen in GMT angegebenen Zeitwert wie folgt zurückgibt.

select now();

Ausgang:

timezone (timestamp with timezone)
2022-04-27 21:55:14.098115+05

Daher ist es wichtig, UTC anzugeben, wenn Sie den TIME-Wert erhalten. In vielen Fällen werden bei Verwendung von TIMESTAMP die TIME-Werte des Systems in UTC gespeichert, ansonsten anders angegeben.

Außerdem kann es eine implizite Konvertierung entweder von lokalen zu UTC-Daten oder umgekehrt geben.

Erstellen Sie Tabellen mit der Spalte TIMESTAMP im UTC-Format

Eine beliebte Methode zum EINFÜGEN von Daten in eine Tabelle, in der die Datensatzinformationen die gültige Zeit enthalten müssen, besteht darin, einen automatischen Zeithandler hinzuzufügen, eine Spalte, die aus dem aktualisierten aktuellen DATUM UHRZEIT besteht und diese speichert, sobald der Datensatz EINGEFÜGT ist. .

Wir können TIMESTAMP WITHOUT TIME ZONE, ein Alias für UTC, verwenden, um eine Spalte anzugeben, die UTC-Werte auf INSERTION enthält. Schreiben wir dazu eine Abfrage wie folgt.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default (now() at time zone('utc')), rec int);

Durch Ausführen der obigen Abfrage wird eine temporäre Tabelle erstellt, die eine Spalte DEFAULT TIMESTAMP enthält. Wenn wir in der obigen Tabelle INSERT aufrufen, enthält jeder eingefügte Datensatz einen automatischen TIMESTAMP, der in UTC generiert und angehängt wird.

Führen Sie eine Abfrage wie folgt aus.

insert into DELFTSTACK_TUT (rec) VALUES(1), (2), (3);

Es wird uns eine Tabelle wie folgt in der Ausgabe zurückgeben.

timestamp without time zone    rec
"2022-04-27 17:13:31.159356"	1
"2022-04-27 17:13:31.159356"	2
"2022-04-27 17:13:31.159356"	3

Daher können wir sehen, dass die Verwendung des Ausdrucks NOW() mit UTC in der Klausel DEFAULT perfekt funktioniert, um Tabellen zu erstellen, in denen Aufzeichnungen erforderlich sind.

Achten Sie darauf, die Klammern um die Klausel DEFAULT nicht auszulassen, da sie den gesamten Ausdruck als einzelnes Element aufnehmen und dann verwenden muss, um die Werte in den entsprechenden Datensätzen zu füllen.

In verschiedenen Versionen von PostgreSQL können unterschiedliche Probleme auftreten. Manchmal kann ein Fehler bei der Definition der ZEITZONE auftreten.

Das Schreiben von UTC anstelle von utc kann aufgrund unterschiedlicher CASE zu Problemen führen. Daher ist es wichtig, beim Schreiben solcher Ausdrücke in PostgreSQL auf CASE SENSITIVE EXPRESSIONS und CONSTRAINTS zu achten.

Verwenden Sie TIMEZONE(), um TIME im UTC-Format für die Tabellenspalte in PostgreSQL zu erhalten

Eine weitere einfache Alternative, um den Wert von TIME DATE im UTC-Format für unsere Spalte zu erhalten, ist der Aufruf einer Abfrage.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default ('utc', now()), rec int);

Dies wird jedoch beim CREATING einen Fehler wie folgt zurückgeben.

Ausgang:

ERROR:  column "time_stamp" is of type timestamp without time zone but default expression is of type record
HINT:  You will need to rewrite or cast the expression.
SQL state: 42804

Der Fehler wird durch die Nichtübereinstimmung der Typen verursacht. Sie können entweder CAST oder den obigen Ausdruck umschreiben, um diesen Fehler zu beseitigen.

Wir werden unsere Abfrage jedoch auf zwei verschiedene Arten CAST, damit Sie sie besser verstehen. Der erste unten konvertiert diesen TIMESTAMP in einen anderen TIMEZONE, der mit dem UTC-Parameter bereitgestellt wird.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default timezone('utc', now()), rec int);

Dadurch erhalten wir eine Tabelle wie folgt.

Ausgang:

timestamp without time zone    rec
"2022-04-27 17:45:34.257072"	1
"2022-04-27 17:45:34.257072"	2
"2022-04-27 17:45:34.257072"	3

Und unser zweiter Workaround, CASTS, diesen Ausdruck zu TEXT und dann die Ausgabe nehmen, falls es Unterschiede zwischen verschiedenen PostgreSQL-Versionen geben sollte.

create temporary table DELFTSTACK_TUT(time_stamp  timestamp without time zone default now()::timestamp, rec int);

Wenn Sie das obige ausführen und dann INSERTING 3-Datensätze in die Tabelle einfügen, erhalten Sie die folgende Ausgabe.

timestamp without time zone    rec
"2022-04-27 22:49:51.654846"	1
"2022-04-27 22:49:51.654846"	2
"2022-04-27 22:49:51.654846"	3

Daher können Sie feststellen, dass das Umwandeln unseres NOW() in einen TIMESTAMP und das anschließende Einfügen in eine mit UTC angegebene Spalte uns die richtigen Ergebnisse liefert. Diese letztere Lösung berücksichtigt jedoch keine UTC-Offsets und behält nur einen naiven Zeitstempel als Aufzeichnung.

Verwenden Sie INTERVAL -8:00 statt UTC

Sie können Ihre Abfrage wie folgt schreiben, wenn Sie nicht in CASE-Problemen beim Schreiben von UTC in Ihrer Sitzung stecken bleiben möchten.

create temporary table DELFTSTACK_TUT(time_stamp timestamp without time zone default timezone(INTERVAL '+00:00', now()), rec int);

Wenn Sie diese Abfrage ausführen, die Tabelle erstellen und INSERT aufrufen, werden die korrekten Ergebnisse im UTC-Format zurückgegeben. INTERVAL mit +00:00 ist in der PostgreSQL-Dokumentation als zur Zeitzone UTC oder ZULU (militärische Abkürzung) mit einem Offset von +00.00 gehörend definiert.

Ein Ergebnis wird wie folgt generiert.

 timestamp without time zone   rec
"2022-04-27 17:57:36.054746"	1
"2022-04-27 17:57:36.054746"	2
"2022-04-27 17:57:36.054746"	3

Verpacken Sie das Zeitformat UTC für eine bessere Effizienz in eine Funktion

Wir können eine einfache Funktion erstellen, um die aktuelle Zeit in UTC zu erhalten, und sie dann mit unserem Datensatz INSERTIONS verwenden.

create function get_UTC_time()
returns timestamp as
$$
  select now() at time zone 'utc';
$$
language sql;

Rufen Sie dann die Abfrage SELECT auf, um die aktuelle Uhrzeit wie folgt zu erhalten.

select * from get_UTC_time();

Dies gibt das folgende Ergebnis aus.

timestamp without time zone
"2022-04-27 18:01:35.42119"

Wir können dies auch in unserer Tabelle verwenden als:

create temporary table DELFTSTACK_TUT(time_stamp  timestamp without time zone default get_UTC_time());

Daher haben wir uns die verschiedenen Möglichkeiten angesehen, die Zeit im UTC-Format für unsere PostgreSQL-Sitzungen zu erhalten. Stellen Sie sicher, dass Sie diese entsprechend Ihren PostgreSQL-Installationen oder -Versionen ändern.

Wir versuchen jedoch unser Bestes, um jede Anfrage und ihre Bedingungen und Einschränkungen so detailliert wie möglich abzudecken.