Timezone offsets in oracle

Question

Using Oracle 9.2i I need to get the offset of various dates from one timezones in relation to another timezone, previously I did this as such

select
(TO_DATE('10-Oct-2010 09:00:00','DD-Mon-YYYY HH24:Mi:SS') - 
              TO_DATE(TO_CHAR(FROM_TZ(to_timestamp( '10-Oct-2010 09:00:00','DD-Mon-YYYY HH24:Mi:SS'), 'Australia/Victoria')
                AT TIME ZONE 'Australia/West'        , 'DD-Mon-YYYY HH24:Mi:SS'),'DD-Mon-YYYY HH24:Mi:SS'))
from dual

Which while a bit messy, worked fine for my needs. Now the problem is this doesn't seem to be taking into account daylight savings. The time difference between Victoria and West Australia on the 10th Oct is actually 3 hours, not 2 hours ( as my query currently returns ).

Now this is odd, as I thought FROM_TZ is mean to handle DST. And even stranger, if I compare to GMT I get the correct results..

select FROM_TZ(to_timestamp( '10-Oct-2010 00:00:00','DD-Mon-YYYY HH24:Mi:SS'), 'GMT') AT TIME ZONE 'Australia/West'
from dual

FROM_TZ(TO_TIMESTAMP('10-OCT-2 
------------------------------ 
10-OCT-10 08.00.00.000000000 AM AUSTRALIA/WEST 

Correct

select FROM_TZ(to_timestamp( '10-Oct-2010 00:00:00','DD-Mon-YYYY HH24:Mi:SS'), 'GMT') AT TIME ZONE 'Australia/Victoria'
from dual

FROM_TZ(TO_TIMESTAMP('10-OCT-2 
------------------------------ 
10-OCT-10 11.00.00.000000000 AM AUSTRALIA/VICTORIA 

Correct

select FROM_TZ(to_timestamp( '10-Oct-2010 00:00:00','DD-Mon-YYYY HH24:Mi:SS'), 'Australia/Victoria') AT TIME ZONE 'Australia/West'
from dual

FROM_TZ(TO_TIMESTAMP('10-OCT-2 
------------------------------ 
09-OCT-10 10.00.00.000000000 PM AUSTRALIA/WEST 

Wrong. 10-Oct-2010 00:00:00 AM in Victoria is 09-OCT-10 09.00.00 PM in West Australia.

So the question is, is this a bug? or am I simply using this FROM_TZ .. AT TIME ZONE wrong?

Thanks.

Update:

I think this might be a bug in the TO_CHAR function, it seems to be getting the wrong TZ offset. While FROM_TZ can correctly change from GMT to Victoria time, when you try to extract the TZ offset from that, it says its +10, when it should be +11.

select TO_CHAR(FROM_TZ(to_timestamp( '10-Oct-2010 00:00:00','DD-Mon-YYYY HH24:Mi:SS'), 'Australia/Victoria'), 'TZH:TZM')
      ,FROM_TZ(to_timestamp( '10-Oct-2010 00:00:00','DD-Mon-YYYY HH24:Mi:SS'), 'GMT') AT TIME ZONE 'Australia/Victoria'
from dual

TO_CHAR(FROM_TZ(TO_TIMESTAMP('                                              FROM_TZ(TO_TIMESTAMP('10-OCT-2 
--------------------------------------------------------------------------- ------------------------------ 
+10:00                                                                      10-OCT-10 11.00.00.000000000 AM AUSTRALIA/VICTORIA 

Answer 1

As far as I can tell, TZH and TZM are abbreviated time zone strings, but they don't take into account daylight savings. Victoria's time zone is +10:00.

Perhaps TZD is what you're after.

TZD Daylight savings information. The TZD value is an abbreviated time zone string with daylight savings information.

http://download.oracle.com/docs/cd/B28359_01/olap.111/b28126/dml_commands_1029.htm

select TO_CHAR(FROM_TZ(to_timestamp( '10-Oct-2010 00:00:00','DD-Mon-YYYY HH24:Mi:SS'), 'Australia/Victoria'), 'TZH:TZM TZD')
      ,FROM_TZ(to_timestamp( '10-Oct-2010 00:00:00','DD-Mon-YYYY HH24:Mi:SS'), 'GMT') AT TIME ZONE 'Australia/Victoria'
from dual

+10:00 EST    10/10/2010 10:00:00.000000000 AM +10:00