Re: TO_TIMESTAMP_TZ and unix timestamps

SuperMega wrote:

> 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

The problem with trying to take into account daylight savings time is that it doesn't actually exist. Well it may where you live. But it doesn't in many countries, in some states, and even in some parts of some states. How's the poor CPU to know?

I think you'll have to write this one yourself using local rules.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)