Converting unix timestamp to date after Daylight Saving Time change
adammsvkOct 31 2011 — edited Oct 31 2011Hello,
in one of the products we use the internal scheduler stores dates (and times) in an Unix-like format, that is seconds since 1st of January 1970. We have used this SQL to extract the actual date:
TO_CHAR(t.next_fire_time/(24*60*60*1000) +to_date('19700101020000','yyyymmddhh24miss'),'yyyymmddhh24miss')
Everything worked fine until the time has changed this weekend from UTC+2 to UTC+1 (we are located in Eastern Europe). Since then the query above returns one hour later than it should.
I.e. timestamp *1320067800000* is returned as *20111031153000* even thought correct is (and the actual time the event was scheduled) *31-OCT-11 02.30.16.000000 PM*
I found queries like this on different forums and no one seems to worry much about the daylight savings. Is this a configuration issue in the DB or is it a bug in our code?
I tried selecting the SESSIONTIMEZONE and DBTIMEZONE and both were +1.
Any help appreciated.