CODEWITHSUNDEEP
oracle-databasetimezone
Ariox66
Ariox66

Reputation: 660

Convert UTC timestamp to AEDT in Oracle to consider daylight saving

Converting from UTC to AEST (Australian Eastern Standard Time) doesn't consider daylight saving as it's +11 hrs now, but this script still converts with 10 hrs :

Select from_tz (cast(DateField as TIMESTAMP),'UTC') at Time Zone 'Australia/Sydney' as AEST

Is there any intuitive way to make it 11 hours in Summer and 10 hours in winter without having to create a function and hard coding it?

Upvotes: 1

Views: 1258

Answers (1)

user5683823
user5683823

Reputation:

Not sure how you determined that your formula "doesn't consider daylight savings". It does on my system. Note that the date literal assumes the time-of-day is midnight. Then compare:

select from_tz (cast(datefield as timestamp),'UTC') 
       at time zone 'Australia/Sydney' as aest
from   ( select date '2020-07-31' as datefield from dual union all
         select date '2020-12-15'              from dual
       )
;
        
AEST                                
------------------------------------
2020-07-31 10:00:00 Australia/Sydney
2020-12-15 11:00:00 Australia/Sydney

Obviously Oracle is aware of the difference between "summer" and "winter" times (DST adjustment).

Upvotes: 2

Related Questions