敗家貓

Converting timestamps between timezomes

Problem
=======
There are many reasons why you might want to convert a 'timestamp' from 1
timezone to another. For example you might have traditionaly stored all times
as GMT times, but you want to know what those timestamps are in Paris.
Because different timezones have different rules regarding the changing
of Daylight Saving Time (or no DST at all) this is not as trivial as it might
seem at first.

DATE datatype v. TIMESTAMP datatype
===================================
The traditional DATE datatype does not work with different timezones at all and
never will. The only way to introduce timezone calculations in your data is by
using TIMESTAMP data. If you cannot change your system then you can still use
TIMESTAMP calculations before you cast the result back to a DATE, as is shown
at the end of this note.

Completely automated timezone calculations
==========================================
Before we move to "how to convert timestamps from one timezone into another"
first a word on compeltely automating this process:

If your database requires a lot of timezone calculations and people from
different timezones might all be inserting their own timestamps, then the best
TIMESTAMP datatype to use is the "TIMESTAMP WITH LOCAL TIMEZONE".
When these types of timestamps are stored they are all 'normalized' to the
database timezone. When they are retieved they are calculated back into the
current session timezone. For example: We have 2 users, Joe in the Paris who's
session runs in timezone "CET" and Mark in Ney York who's timezone is "EST" and
the database timezone is GMT. Joe inserts "01-DEC-2003 14:00:00" from his
session, the value that is stored in the database is changed from the CET time
into GMT, which means "01-DEC-2003 13:00:00" is stored. When either Joe or
Mark retrieve this value it's simple for Oracle to calculate the correct time
in the users timezone. So Joe would get the same "01-DEC-2003 14:00:00" that he
stored back, whereas Mark receives "01-DEC-2003 08:00:00"


How to convert timestamps from one timezone into another timezone
=================================================================
In this example we will asume that we have always stored dates in the GMT
timezone and we want to retrieve the correct times in Paris. It is important
to know that the offset from GMT in Paris is either 1 or 2 hours, depending
on DST.

You may start with simply having a particar date or timestamp. You know that
is was stored in a particular timezone, but the database has no knowledge about
this.

The first thing to do is to use the FROM_TZ function, basicaly to inform the
database of the timezone this timestamp is in. The FROM_TZ function takes
a timestamp and a timezome and adds these together. For example:

SQL> SELECT FROM_TZ(TIMESTAMP '2002-01-29 08:00:00', 'GMT') FROM DUAL;

FROM_TZ(TIMESTAMP'2002-01-2908:00:00','GMT')
-------------------------------------------------------------------------
29-JAN-02 08.00.00.000000000 AM GMT

That is of course not what we want - we want to know what time that means in
Paris, to do that we can add the "AT TIME ZONE" opperator, which calculates the
given time at the base timezone to the correct time in the new timezone:

SQL> SELECT FROM_TZ(TIMESTAMP '2002-01-29 08:00:00', 'GMT') AT TIME ZONE 'CET' FROM DUAL;

FROM_TZ(TIMESTAMP'2002-01-2908:00:00','GMT')ATTIMEZONE'CET'
--------------------------------------------------------------------
29-JAN-02 09.00.00.000000000 AM CET

To show that the timezone calculations take DST into account we can do a
example just before and just after the start of DST in 2002:

SQL> SELECT FROM_TZ(TIMESTAMP '2002-03-31 00:59:00', 'GMT') AT TIME ZONE 'CET' FROM DUAL;

FROM_TZ(TIMESTAMP'2002-03-3100:59:00','GMT')ATTIMEZONE'CET'
--------------------------------------------------------------
31-MAR-02 01.59.00.000000000 AM CET

SQL> SELECT FROM_TZ(TIMESTAMP '2002-03-31 01:00:00', 'GMT') AT TIME ZONE 'CET' FROM DUAL;

FROM_TZ(TIMESTAMP'2002-03-3101:00:00','GMT')ATTIMEZONE'CET'
--------------------------------------------------------------
31-MAR-02 03.00.00.000000000 AM CET


Notice how the time in Paris changes by 1 hour and 1 minute when GMT only moves
forward by 1 minute.


Casting a TIMESTAMP (back) to a DATE
====================================
If you really want to cast the resulting timestamp back into a date you can use
the TO_DATE function for this. The trick is to set the NLS_TIMESTAMP_TZ_FORMAT
first to a setting that the TO_DATE function can so something with (so
something that doesn't include mention of timezones etc). For example:

First of all we'll set the NLS_DATE_FORMAT so that we can see the complete date
that will be returned:
SQL> alter session set NLS_DATE_FORMAT='DD/MM/RR HH:MI:SS AM';

And we set NLS_TIMESTAMP_TZ_FORMAT in such a way that we can use the same format
in the TO_DATE function:
SQL> alter session set NLS_TIMESTAMP_TZ_FORMAT='DD-MON-YYYY HH24:MI:SS';
SQL> SELECT TO_DATE(FROM_TZ(TIMESTAMP'2002-03-31 01:00:00', 'GMT') AT TIME ZONE 'CET', 'DD-MON-YYYY HH24:MI:SS') FROM DUAL;

TO_DATE(FROM_TZ(TIME
--------------------
31/03/02 03:00:00 AM


Considerations
==============
Instead of moving a timestamp like this to a specific timezone, you can also
move it to "the session timezone". in order to do this you need to specify  
" AT LOCAL " instead of " AT TIME ZONE '<tz>' ".
In that case you need to make sure that the session timezone is set correctly.
For more information about that please see the notes mentioned below.