Daylight Saving Time support in Oracle with timestamp with time zone

Daylight Saving Time is handle by Oracle automatic according to specified time zone set in Oracle Database.

Oracle use TZR and TZD format. TZR represent the time zone region with datatime input string. e.g ‘Australia/North‘, ‘UTC‘, and ‘Singapore‘. TZD represent the time zone region with Daylight saving time. e.g ‘PST‘ for U. S. Pacific Standard Time and ‘PDT‘ for U. S. Pacific Daylight Time.

Check the TZR and TZD format.

select tzname,tzabbrev from v$timezone_names;

Check the dbtimezone and session time zone in Oracle

SELECT dbtimezone FROM DUAL;

SELECT sessiontimezone FROM DUAL;

Datatype used for Daylight Saving time as TIMESTAMP WITH TIME ZONE and TIMESTAMP WITH LOCAL TIME ZONE data types.

Note: Only Timestamp datatype has no effect of daylight saving.

Example of using the daylight Saving on 2021 daylight saving happen at 7 Nov 2:00 AM.

SQL> create table test ( testdate1 timestamp, testdate2 timestamp with time zone);
T
able created.

SQL> insert into test values ('28-OCT-00 11:24:54 PM','28-OCT-00 11:24:54 PM America/New_York');
1 row created.

SQL> insert into test values ('06-NOV-21 11:30:00 PM','06-NOV-21 11:30:00 PM America/New_York');

1 row created.
--- See the time difference after 8 hours for both dates
SQL> SELECT testdate1 + INTERVAL '8' HOUR, testdate2 + INTERVAL '8' HOUR FROM test;

TESTDATE1+INTERVAL'8'HOUR
---------------------------------------------------------------------------
TESTDATE2+INTERVAL'8'HOUR
---------------------------------------------------------------------------
29-OCT-00 07.24.54.000000000 AM
29-OCT-00 06.24.54.000000000 AM AMERICA/NEW_YORK

07-NOV-21 07.30.00.000000000 AM
07-NOV-21 06.30.00.000000000 AM AMERICA/NEW_YORK

SQL> drop table test;