Oracle Timestamp Processing – mildly annoying

I was writing a small piece of SQL this morning which I needed to account for daylight savings time correctly. All of my databases run in UTC, so a quick foray into using TIMESTAMP AS TIME ZONE seemed the easiest way to accomplish this. So, I code it up and want to test my code to ensure that the timestamp operates correctly on both sides of UK Daylight savings. I figured that the easiest way to do this would be to used the old Oracle initialisation parameter FIXED_DATE. You can set this in the database on the fly and observe the results immediately. So to for testing (in a Dev database which only I was using). Guess what? FIXED_DATE works perfectly for SYSDATE. However, it is completely ignored for SYSTIMESTAMP! WHAT??? Who in Oracle missed this one? Let me show you how this (doesn’t) work, with a workaround for my testing included in the example to show how neatly TIMESTAMP AS TIME ZONE does work:

> alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS'

Session altered.

> select * from v$timezone_names where tzname like '%London%'

TZNAME                                                           TZABBREV
---------------------------------------------------------------- ----------------------------------------------------------------
Europe/London                                                    LMT
Europe/London                                                    GMT
Europe/London                                                    BST
Europe/London                                                    BDST

Before we start, check the current date
> select sysdate from dual

SYSDATE
--------------------
01-NOV-2011 11:50:15

Before Daylight Savings Time changes (on 29th October 02:00:00)
> alter system set fixed_date = '28-OCT-2011 08:00:00' scope=memory

System altered.

> select systimestamp at time zone 'Europe/London' from dual

SYSTIMESTAMPATTIMEZONE'EUROPE/LONDON'
---------------------------------------------------------------------------
01-NOV-11 11.50.15.666000 EUROPE/LONDON

The timestamp is unaffected by setting FIXED_DATE!!! Oracle! Grrrr!

> select sysdate from dual

SYSDATE
--------------------
28-OCT-2011 08:00:00

The UTC time is correct.

So I need to take the sysdate and transform it into a timestamp at timezone
London should be 1 hour ahead of UTC at this point
> select to_timestamp(sysdate) at time zone 'Europe/London' from dual

TO_TIMESTAMP(SYSDATE)ATTIMEZONE'EUROPE/LONDON'
---------------------------------------------------------------------------
28-OCT-11 09.00.00 EUROPE/LONDON

###########################################################################
Now to roll the time on and re-test
###########################################################################
After Daylight Savings Time changes (on 29th October 02:00:00)
> alter system set fixed_date = '30-OCT-2011 08:00:00' scope=memory

System altered.

> select sysdate from dual

SYSDATE
--------------------
30-OCT-2011 08:00:00

Now Daylight savings should by the same as UTC, not 1 hour ahead
> select to_timestamp(sysdate) at time zone 'Europe/London' from dual

TO_TIMESTAMP(SYSDATE)ATTIMEZONE'EUROPE/LONDON'
---------------------------------------------------------------------------
30-OCT-11 08.00.00 EUROPE/LONDON

Yey! Daylight davings is correct for London. 

Remove the fixed_date setting
> alter system set fixed_date=NONE scope=memory

System altered.

And check the date
> select sysdate from dual

SYSDATE
--------------------
01-NOV-2011 11:50:16
------------------------------------------------------------

There you have it. How very mildly annoying. Can’t use TIME ZONE with SYSDATE, can’t use FIXED_DATE with SYSTIMESTAMP.