TO_TIMESTAMP_TZ and unix timestamps

From: SuperMega <jagacontest_at_hotmail.com>
Date: 26 Jan 2005 09:52:56 -0800
Message-ID: <1106761976.814351.33960@c13g2000cwb.googlegroups.com>

I have a table with a column filled with unix timestamps (how many seconds from whatever 1970) When people access that field it is not taking into account daylight saving time. Originally I was tasked with writing a huge case statement that would subtract an hour based on the time of year. I did a little digging and found the TO_TIMESTAMP_TZ function and read that it will handle all daylight saving time adjustments. So i set my timezone and restarted the db.

SQL> select dbtimezone from dual;

DBTI

---

EST Then I made my TO_TIMESTAMP_TZ statement, 2 of them one a couple days before the DST change and one a couple days after(in 24 hour increments), thinking that the 2nd one would display and hour before, but no change.

SQL>SELECT TO_TIMESTAMP_TZ('01/01/1970 00:00:00 EST', 'mm/dd/yyyy HH24:MI:SS TZD')
+ ((1112390687 / 86400) - (18000/86400))
FROM dual;
TO_TIMESTAMP_TZ('01/

---

Apr 01 2005 16:24:47

SQL>SELECT TO_TIMESTAMP_TZ('01/01/1970 00:00:00 EST', 'mm/dd/yyyy HH24:MI:SS TZD')
+ ((1112649887/86400) - (18000/86400)) FROM dual;

TO_TIMESTAMP_TZ('01/

---

Apr 04 2005 16:24:47
Has anyone had any experience with this in the past?

--SM Received on Wed Jan 26 2005 - 11:52:56 CST