Oracle changing timestamp column value for Daylight Savings

user_11554030 0
Viewed 89

I have a timestamp column which has value 12/02/2019 02:00:00. But when the months of daylight savings come the value automatically changes to 12/02/2019 01:00:00.Now if I make the timezone to Europe/Istanbul it becomes the original value. But I have to keep changing the timezone every few months when daylight savings happens.I need the query to return the original value. Again for normal months I have to set the timezone to Asia/Istanbul otherwise the value changes forward.

plsql oracle
1 Answers

If your column is defined with the timestamp with local time zone data type, it will be stored with the database time zone and displayed with the session timezone.

See how the displayed value (not the stored value) for column C3 changes in this example from 20:28 to 21:28:

SQL> create table t (
   c1 timestamp,
   c2 timestamp with time zone,
   c3 timestamp with local time zone
);

SQL> alter session set time_zone = 'Europe/Rome';

SQL> insert into t (c1, c2, c3) values (systimestamp, systimestamp, systimestamp);

SQL> select * from t;

C1                                                 C2                                  C3                                 
-------------------------------------------------- ----------------------------------- -----------------------------------
20/08/2020 20:28:17.942586                         20/08/2020 20:28:17.942586 +02:00   20/08/2020 20:28:17.942586

SQL> alter session set time_zone = 'Europe/Istanbul';

SQL> select * from t;

C1                                                 C2                                  C3                                 
-------------------------------------------------- ----------------------------------- -----------------------------------
20/08/2020 20:28:17.942586                         20/08/2020 20:28:17.942586 +02:00   20/08/2020 21:28:17.942586
user_21985840
Related