DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: TO_TIMESTAMP_TZ and unix timestamps

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    103

    TO_TIMESTAMP_TZ and unix timestamps

    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, 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

    Anyone have any experience with this in the past?

    SM

  2. #2
    Join Date
    Oct 2000
    Posts
    103
    *BUMP*

    anyone.....anyone....Bueller ???

  3. #3
    Join Date
    Oct 2000
    Posts
    103
    wow!
    im surprised no one has taken a stab at this one

    --SM

  4. #4
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    ====
    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
    ========

    What is the result you expect here?

    You are adding a number after a date is formatted with TO_TIMESTAMP_TZ function.

    Very few people use TO_TIMESTAMP_TZ format in real system. That is why you do not answer.

    Hmm, Is TO_TIMESTAMP_TZ for OCP only ?

    Tamil

  5. #5
    Join Date
    Oct 2000
    Posts
    103
    hmmm... i see your point about doing the math after the timestamp conversion. i was doing it there because someone else tested it and found that if you did math in it the procedure returned a date instead of timestamp and the DST wasnt taken into account. my next guess would be do the math in a to_date and assign that to a variable and pass that variable to a to_timestamp_tz.

    what i am hoping is if i pass Apr 04 2005 09:00 to a to_timestamp_tz oracle would return the time being 08:00 because it the date is in daylight daving time.

    Thanks in advance,
    --SM
    Last edited by supermega; 02-09-2005 at 11:34 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width