CODEWITHSUNDEEP
oracle-databaseplsql
Hriday Pratim Bharali
Hriday Pratim Bharali

Reputation: 107

Oracle changing timestamp column value for Daylight Savings

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.

Upvotes: 4

Views: 160

Answers (1)

Marco Baldelli
Marco Baldelli

Reputation: 3728

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

Upvotes: 1

Related Questions