Timezone - totale Konfusion

Hallo zusammen,

ich schlage mich gerade mit dem Zusammenhang Timestamp und Timezone rum.

Mein eigentlicher Plan war es, in der Datenbank ein Feld vom Typ Timestamp OHNE Zeitzone zu verwenden. In meiner Anwendung wandele ich alle Timestamps bereits in UTC Zeit und schreib das in die Datenbank - um das leidige Sommer/Winterzeit-Problem zu umgehen. Wenn ich einen Typ Timestamp (ohne Timezone) verwende, wird das automatisch als UTC verstanden, oder?


Ein echtes Problem habe ich beim Rausholen des Timestamps aus der Datenbank. Soweit ich das bisher sehe, funktioniert das sogar so, wie ich mir das dachte, sprich mit Select meinezeit AS TIME ZONE ‘CET’ beachtet er dann sogar Sommer und Winterzeit. Zumindest zählt er wenn ich ein Datum vom Juni letzten Jahres hole 2 Stunden dazu, wenn ich eines vom Dezember hole nur eine. Ist das nur Zufall oder ist das so definiert?
Das Problem beginnt nun aber spätestens bei der ausgegebenen Formatierung. Ausgegeben wird etwas wie:

2006-06-01 12:13:43+02 oder eben im deutschen Format, aber niemals wird der Offset draufgerechnet. Ich will aber nicht die Ausgabe 2006-06-01 12:13:43+02 haben sondern 2006-06-01 14:13:43, also dass er den Offset gleich draufrechnet. Das bekomm ich hin, wenn ich den Typ Timestamptz verwenden - was ich aber eigentlich nicht will. Dann müsste ich nämlich in meiner Anwendung beliebige Zeitstempel schon selbst in die richtige Zeitzone rechnen. Ich bekomme aber irgendwelche Unix-Zeitempel in UTC-Zeit und die will ich eigentlich als Timestamp in die Datenbank schreiben und dann für eine beliebige Zeitzone wieder rausholen. Es geht also hauptsächlich um die Formatierung der Ausgabe - wie bekomm ich es hin, dass der Offset gleich draufaddiert wird?

Hmm, mal wieder verwirrend erklärt :slight_smile: Hoffe trotzdem auf eure Hinweise.

Bierstuebl

Wäre beim Speichern der Daten ein ‘set time zone ‘UTC’;’ und ein speichern als timestamptz eine Lösung?

Andreas

Hmm, du meinst vor jedem Speichern die Timezone setzen? Dann werden aber beim Speichern aus einem Insert 2 Befehler - da die Datenbank sehr schreiblastig ist, würde ich das nur ungern tun. Ich könnst natürlich in ne Function auslager - gilt das Set Timezone dann eigentlich für die ganze Transaktion?

Die Alternative wäre eben wirklich, Postgresql komplet in der Timezone UTC zu fahren. Wäre aber doof, wenn andere die gleiche Instanz benutzen wollen, die das eben nicht wollen. Eigentlich funktioniert ja alles wunderbar mit einem Feld vom Typ Timestamp, bis auf die Ausgabeformatierung :frowning: Das ist aus meiner Sicht das erste echte Manko, das ich bei Postgresql finde (bis auf dieses komische nicht automatisch ausgeführte vacuum analyze). Methoden wie Extract() etc. kann man soweit ich sehe nur auf values anwenden, nicht auf Felder. Das versteh ich gar nicht :frowning:

Ich werd über deine Lösung mit dem Set timezone nachdenken, danke schonmal.

Gruß,
Bierstuebl

set time zone …; insert into …
Wenn das innerhalb einer TX ist, dürfte das auch nur dort gelten.

Methoden wie Extract() etc. kann man soweit ich sehe nur auf values anwenden, nicht auf Felder. Das versteh ich gar nicht :frowning:

Ich auch nicht. Was Du da meinst.

test=*# create table t (ts timestamp);
CREATE TABLE
test=*# insert into t values (now());
INSERT 0 1
test=*# select extract (dow from ts) from t;
 date_part
-----------
         5
(1 row)

Andreas

Hmm, ok, das extract funzt wirklich auch auf Felder, keine Ahnung, warum es gestern nicht getan hat, hab immer nen Syntaxerror bekommen, sobald ich ‘2006-…’ durch nen Feldname ersetzt hab.

haha, folgender Hack funktioniert so wies aussieht:

Wenn zeit ein Feld vom Typ Timestamp ohne Zeitzone ist und UTC repräsentiert, dann funktioniert folgendes:

select timezone(‘CET’, zeit AT TIME ZONE ‘UTC’) from…

zeit wird also zuerst in einen Timestamp MIT Zeitzone UTC umgewandelt und anschließen nochmals in CET :slight_smile:

Damit funktioniert die Ausgabeformatierung - ob man damit auch gescheite Suchanfragen stellen kann wird sich zeigen…