Oracle Database Discussions

Hi All,

We are trying to convert date from Eurpoer/Amsterdam timezone to Australia/Sydnet timezone and extracting time out of it.
We are facing issue related to incorrect timing after conversion.

Please find below issue details,
Environment
Database: Oracle 10.2.0.4
Machine: Linux RHEL 4
Location: Amsterdam, Netherlands

Issue: After converting date from Eurpoer/Amsterdam timezone to Australia/Sydnet timezone

SELECT TO_CHAR (FROM_TZ (TO_TIMESTAMP ('201110201416', 'YYYYMMDDHH24MI'),

DBTIMEZONE

) AT TIME ZONE 'Australia/Sydney', 'HH24:MI')

FROM DUAL



Output of above SQL is 22:16.

Expected output is 23:16



Database Timezone (DBTIMEZONE) is set to +02:00 (i.e. GMT + 2) (Europe/Amsterdam)



If we convert date to Australia/Tasmania timezone then we get expected output i.e. 23:16.



Question: Expected time for Australia/sydney is 23:16, but why Oracle database provides 22:16 as output.

Please note that, Sydney and Tasmania comes under same timezone. Still we get different output for both the timezone.

Oracle database considers GMT + 10 for Australia/sydney and GMT + 11 for Australia/Tasmania.



If we want to use Australia/sydney then how should we get correct sydney time?



Regards

Shailendra