CODEWITHSUNDEEP
oracle-databasetimezone
ranjit unnikrishnan
ranjit unnikrishnan

Reputation: 15

Oracle Timezone Conversion

I am using the inbuilt oracle function from_tz for timezone conversion. Does anyone know if it takes into account daylight saving changes?

 to_char(from_tz(CAST(enteredondtm AS TIMESTAMP),'UTC') 
         AT TIME ZONE 'PST','YYYY-MM-DD') entered_on_date .

in this function the enteredondtm is in UTC which is converted to PST with this function. The conversion happens correctly. Just wanted to make sure it will happen during daylight savings.

Thanks.

Upvotes: 1

Views: 3483

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59446

Yes it does, simple proof:

SELECT 
    TIMESTAMP '2018-06-01 00:00:00 PST',
    TIMESTAMP '2018-12-01 00:00:00 PST'
from dual;

01.06.2018 00:00:00 -07:00  01.12.2018 00:00:00 -08:00

However, why are you concerned about daylight savings if your output is YYYY-MM-DD, i.e. it does not contain any times?

Just a note, time zone PST can be ambiguous, see

SELECT TZABBREV, TZ_OFFSET(TZNAME), TZNAME
FROM V$TIMEZONE_NAMES
WHERE TZABBREV = 'PST'; 

TZABBREV    TZ_OFFSET(TZNAME)   TZNAME
========================================
PST -05:00  America/Bahia_Banderas
PST -06:00  America/Mazatlan
PST -06:00  America/Boise
PST -06:00  Mexico/BajaSur
PST -06:00  America/Inuvik
PST -07:00  America/Los_Angeles
PST -07:00  America/Hermosillo
PST -07:00  America/Ensenada
PST -07:00  America/Dawson_Creek
PST -07:00  America/Dawson
PST -07:00  America/Creston
PST -07:00  America/Santa_Isabel
PST -07:00  US/Pacific
PST -07:00  PST8PDT
PST -07:00  PST
PST -07:00  US/Pacific-New
PST -07:00  America/Tijuana
PST -07:00  America/Vancouver
PST -07:00  America/Whitehorse
PST -07:00  Canada/Pacific
PST -07:00  Canada/Yukon
PST -07:00  Mexico/BajaNorte
PST -08:00  America/Juneau
PST -08:00  America/Sitka
PST -08:00  Pacific/Pitcairn
PST -08:00  America/Metlakatla

Better use the "real" long name. There are only a few time zones (see SELECT * FROM V$TIMEZONE_NAMES WHERE TZABBREV = TZNAME) where you can use the abbreviation without getting a ORA-01882: timezone region not found error.

Upvotes: 1

user5683823
user5683823

Reputation:

Yes, it will take DST into consideration.

To convince yourself of that, substitute date '2018-02-01' for the column name enteredondtm in your formula and see what you get (use a full format model for TO_CHAR though, to see the time-of-day, not just the date). You will see that the output is 16:00 hours on the previous day. Then change that to date '2018-08-01' and you will see that the result is 17:00 hours on the previous day.

The date date literal has midnight as the time-of-day. During standard time, the Pacific time zone is eight hours behind UTC; during DST, it is seven hours behind.

Upvotes: 2

Related Questions