2. November 2009

DATE, TIMESTAMP und Zeitzonen in SQL und PL/SQL: Wissenswertes

English title: DATE, TIMESTAMP and time zones in SQL and PLSQL: Things to know - contd ...

Heute setze ich das vor einigen Wochen begonnene Posting zum Thema DATE und TIMESTAMP fort. Wie darin bereits erläutert, unterscheidet sich TIMESTAMP durch zwei Dinge von DATE. Erstens kann TIMESTAMP mit Sekundenbruchteilen umgehen, zweitens werden Zeitzonen unterstützt. Ein Blick in die Dokumentation zeigt nun, dass es TIMESTAMP in drei Varianten gibt:
Today I'd like to continue the blog posting about DATE and TIMESTAMP which I published a few weeks ago. As stated in this posting there are basically two differences between DATE and TIMESTAMP. Firstly, TIMESTAMP supports fractional seconds, secondly TIMESTAMP can handle time zones. The documentation states that there are three variants of TIMESTAMP:
  • TIMESTAMP
  • TIMESTAMP WITH TIME ZONE
  • TIMESTAMP WITH LOCAL TIME ZONE
Der einfache TIMESTAMP arbeitet ohne Zeitzone (hier besteht der Unterschied zu DATE nur in der Unterstützung von Sekundenbruchteilen). Die anderen beiden Varianten können mit Zeitzonen umgehen. TIMESTAMP WITH TIME ZONE speichert dabei eine explizite Information zur Zeitzone (MEZ, PST, etc.) wogegen TIMESTAMP WITH LOCAL TIME ZONE zur Konvertierung in die "lokale" Zeitzone (die Zeitzone der Datenbanksession) vorgesehen ist (dazu unten mehr). Im letzten Blog-Posting habe ich TIMESTAMP anhand der SYSTIMESTAMP-Funktion vorgestellt. Wenn Ihr wissen möchtet, welche dieser drei Varianten SYSTIMESTAMP nun zurückliefert, könnt Ihr das einfach selbst herausfinden ...
The "simple" TIMESTAMP works without time zones - the only difference between this and a DATE value is the support of fractional seconds. TIMESTAMP WITH TIME ZONE stores the time zone information explicitly whereas TIMESTAMP WITH LOCAL TIME ZONE is suited for datetime conversions to the "local" time zone (the time zone of the database session). In the first blog posting I used the SYSTIMESTAMP function in order to present the TIMESTAMP data type. If you now like to know which of the three TIMESTAMP variants SYSTIMESTAMP returns, just create a view as follows ...
create view systimestamp_type as select systimestamp datum from dual
/

View created.

desc systimestamp_type

 Name                                      Null?    Typ
 ----------------------------------------- -------- ----------------------------

 DATUM                                              TIMESTAMP(6) WITH TIME ZONE
Man sieht, dass SYSTIMESTAMP vom Typ TIMESTAMP WITH TIME ZONE ist. Bei TIMESTAMP könnt Ihr (wie man sieht) auch eine Präzision für die Sekundenbruchteile angeben - der Default ist 6. Die kleinste Einheit in diesem Fall wäre also 0,000001 Sekunden. Diese Präzision kann von Null (0) bis Neun (9) reichen. Wenn Ihr also eine Tabelle mit TIMESTAMP(0) anlegt, habt Ihr effektiv ein DATE.
As you can see: SYSTIMESTAMP returns TIMESTAMP WITH TIME ZONE. And there is another "feature" of TIMESTAMP: You can specify a precision value for the fractional seconds. The default for this is 6 - the smallest unit is then 0.000001 seconds. The range for this goes from zero (0) to nine (9). So if you create a table with a TIMESTAMP(0) column, you have actually a DATE.
TIMESTAMP WITH TIME ZONE bringt nun einige Besonderheiten beim Erzeugen mit TO_TIMESTAMP und umwandeln in VARCHAR2 mit TO_CHAR mit. Zunächst wollen wir das Datum 02.11.2009 14:00 als TIMESTAMP erzeugen, aber nicht in unserer Zeitzone (MEZ), sondern als "kalifornische Zeit". Dazu kann man entweder TO_TIMESTAMP_TZ oder FROM_TZ nutzen.
Time zones must also be taken into account when constructing TIMESTAMP WITH TIME ZONE value or when converting it to VARCHAR2 using TO_CHAR. Let's create a TIMESTAMP value for November, 2nd 2009, 2:00pm - but Pacific Time not CET. You can either use TO_TIMESTAMP_TZ or FROM_TZ for this.
select to_timestamp_tz('2009-11-02 14:00:00 PST', 'YYYY-MM-DD HH24:MI:SS TZR') from dual;

TO_TIMESTAMP_TZ('2009-11-0214:00:00PST','YYYY-MM-DDHH24:MI:SSTZR')
---------------------------------------------------------------------------
02.11.09 14:00:00,000000000 PST

select from_tz(to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 'PST') from dual;

FROM_TZ(TO_TIMESTAMP('2009-11-0214:00:00','YYYY-MM-DDHH24:MI:SS'),'PST')
---------------------------------------------------------------------------
02.11.09 14:00:00,000000000 PST
In beiden Fällen kommt ein TIMESTAMP WITH TIME ZONE heraus; die Information "PST" steckt da also explizit drin - und das können wir nun nutzen, um den Wert in unsere Zeitzone umzurechnen. Dazu bietet TO_CHAR zusätzliche Schlüsselwörter an:
Both functions return TIMESTAMP WITH TIME ZONE; the information that this is "pacific time" is now explicity stored within the TIMESTAMP value. So we now can convert this value to other time zones. The TO_CHAR function has addional keywords for that ...
select 
 to_char(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) at time zone 'CET','DD.MM.YYYY HH24:MI:SS'
 ) as "ZEIT_IN_MEZ" from dual;

ZEIT_IN_MEZ
-------------------
02.11.2009 23:00:00
"Offsets" gehen natürlich auch ...
You can -of course- also work with timezone offsets ...
select 
 to_char(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) at time zone '+01:00','DD.MM.YYYY HH24:MI:SS'
 ) as "ZEIT_IN_MEZ" from dual;

ZEIT_IN_MEZ
-------------------
02.11.2009 23:00:00
Wenn Ihr in die lokale Zeitzone der Session umrechnen wollt, könnt Ihr den Datentypen TIMESTAMP WITH LOCAL TIME ZONE verwenden; das ist nützlich, wenn mehrere Nutzer in unterschiedlichen Zeitzonen mit dem System arbeiten.
If you like to convert to the "local" time zone (the time zone of the database session), you might use TIMESTAMP WITH LOCAL TIME ZONE. This es particularly useful if your application has to deal with different client time zones.
SQL> alter session set time_zone='CET';

Session altered.

select 
 cast(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) as TIMESTAMP with local time zone
 ) as "LOCAL_TIME" 
from dual;

LOCAL_TIME
---------------------------------------------------------------------------
02.11.09 23:00:00,000000

Session altered.

SQL> alter session set time_zone='EST';

select 
 cast(
  from_tz(
   to_timestamp('2009-11-02 14:00:00', 'YYYY-MM-DD HH24:MI:SS'), 
   'PST'
  ) as TIMESTAMP with local time zone
 ) as "LOCAL_TIME" 
from dual;

LOCAL_TIME
---------------------------------------------------------------------------
02.11.09 17:00:00,000000
Wenn Ihr nun wissen möchtet, was "PST" nun konkret bedeutet, kann die Funktion TZ_OFFSET weiterhelfen. Sie gibt wieder, wieviele Stunden eine gegebene Zeitzone von UTC (GMT) abweicht.
If you like to know what "PST" means (how many hours), you might use the TZ_OFFSET function. It returns the offset of a given time zone to UTC.
select tz_offset ('PST') from dual;

TZ_OFFS
-------
-07:00
Man kann also sagen, dass man mit TIMESTAMP (und hier: TIMESTAMP WITH TIME ZONE) arbeiten sollte, wenn es an das Speichern von Zeiten geht. Denn die Wahrscheinlichkeit, dass man verschiedene Zeitzonen unterstützen muss, dürfte (für jedwede Anwendung) in Zukunft eher steigen. Arbeitet man dagegen mit DATE, sollte man zumindest alle Zeiten auf eine Zeitzone normalisieren (am besten UTC, also die "Greenwich-Zeit") und dann bei Bedarf umrechnen.
In summary one should work with the TIMESTAMP (and more accurate: TIMESTAMP WITH TIME ZONE) data type when it's about storing datetime values. In the future it will be more and more likely that an application must support multiple time zones - so you should be better off when anticipating this from the very beginning. If you are working with DATE you should at least "normalize" all values to a specific time zones (say: "UTC") and convert them as needed.

Keine Kommentare:

Beliebte Postings