Oracle database and time zones

Question

I'm working with an oracle database. Every table got a creationdate and lastmodified field. lastmodified is the actual time of the time zone ( UTC+1 + summer time if needed), while creationdate is the same time, but at UTC+0, leaving a difference of 2 hours in summer time and 1 hour otherwise. Is there a way to change the creationdate so it's also using utc+1 + summer time when needed ? Thanks

Answer 1

You can use this one:

ALTER SESSION SET TIME_ZONE = 'Europe/Zurich';
SELECT 
    TO_TIMESTAMP_TZ(TO_CHAR(creationdate,'yyyymmddhh24miss"UTC"'), 'yyyymmddhh24missTZR') AT LOCAL AS creationdate_local_TIMESTAMP,
    CAST(TO_TIMESTAMP_TZ(TO_CHAR(creationdate,'yyyymmddhh24miss"UTC"'), 'yyyymmddhh24missTZR') AT LOCAL AS DATE) AS creationdate_local_DATE  
FROM your_table;

You must set you session time zone to region like above. If you use static value (e.g. ALTER SESSION SET TIME_ZONE = '+02:00';) it will not work properly.

Important note: if you update your table with converted time values, then you must ensure that you don't do it several times, because every update your time would be shifted by 1 or 2 hours again and again.