Re: TO_TIMESTAMP_TZ and unix timestamps

From: Andy Hassall <andy_at_andyh.co.uk>
Date: Tue, 01 Feb 2005 22:38:39 +0000
Message-ID: <a600019iuoncs42r7aqjtvl3ir4pjnslni@4ax.com>

On 26 Jan 2005 09:52:56 -0800, "SuperMega" <jagacontest_at_hotmail.com> 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

It's not a complete answer, but it looks like trying to do date arithmetic (adding integer seconds on) to a TIMESTAMP WITH TIMEZONE ends up converting it to a plain DATE first, losing the timezone.

TO_TIMESTAMP_TZ('01/01/197000:

01/04/2005 16:24:47

This isn't the timestamp string format, this is the plain date format.

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

DUMP(TO_TIMESTAMP_TZ('01/01/19

Typ=13 Len=8: 213,7,4,1,16,24,47,0

Type 13 is the internal DATE format (12 is the external one).

You at least stay with timestamps if you use INTERVAL instead of adding fractional days, but you can only go up to 9 digits for a SECONDS interval.

SQL> SELECT TO_TIMESTAMP_TZ('01/01/1970 00:00:00 EST', 'mm/dd/yyyy HH24:MI:SS TZD')

  2         + INTERVAL '999999999' SECOND(9)
  3         + INTERVAL '112390688' SECOND(9)
  4         - INTERVAL '18000'     SECOND(9)

5 FROM dual;

TO_TIMESTAMP_TZ('01/01/197000:

01-APR-05 04.24.47.000000000 PM +00:00 SQL>
SQL> SELECT TO_TIMESTAMP_TZ('01/01/1970 00:00:00 EST', 'mm/dd/yyyy HH24:MI:SS TZD')

  2         + INTERVAL '999999999' SECOND(9)
  3         + INTERVAL '112649888' SECOND(9)
  4         - INTERVAL '18000'     SECOND(9)

5 FROM dual;

TO_TIMESTAMP_TZ('01/01/197000:

04-APR-05 04.24.47.000000000 PM +00:00 Still no DST transition though; they're coming out in my local time zone (GMT). Changing session time zone may be the thing that does the trick, though:

SQL> alter session set time_zone = 'US/Eastern';

Session altered

SQL> SELECT TO_TIMESTAMP_TZ('01/01/1970 00:00:00 EST', 'mm/dd/yyyy HH24:MI:SS TZD')

  2         + INTERVAL '999999999' SECOND(9)
  3         + INTERVAL '112390688' SECOND(9)
  4         - INTERVAL '18000'     SECOND(9)

5 FROM dual;

TO_TIMESTAMP_TZ('01/01/197000:

01-APR-05 04.24.47.000000000 PM US/EASTERN SQL> SELECT TO_TIMESTAMP_TZ('01/01/1970 00:00:00 EST', 'mm/dd/yyyy HH24:MI:SS TZD')

  2         + INTERVAL '999999999' SECOND(9)
  3         + INTERVAL '112649888' SECOND(9)
  4         - INTERVAL '18000'     SECOND(9)

5 FROM dual;

TO_TIMESTAMP_TZ('01/01/197000:

04-APR-05 05.24.47.000000000 PM US/EASTERN

-- 
Andy Hassall / <andy@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool

Received on Tue Feb 01 2005 - 16:38:39 CST