1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

floor((CAST(SYS_EXTRACT_UTC(systimestamp) AS DATE) incorrect hour

Discussion in 'SQL PL/SQL' started by luffy, May 19, 2011.

  1. luffy

    luffy Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    Hi to all,
    Need help can't seem to understand why the hour is incorrect.
    Below query "dte_computation_on_data" is the old function they use to convert date and insert it to the table. Problem is when I revert it to the actual date the hour
    is incorrect.Need help.
    Code (Text):

    SELECT -- THIS HERE IS MY TEST TO REVERT TIME AND DATE ON THE FORMULA OF WITH RESPECT TO THEIR FUNCTION
    to_char(TO_DATE('19700101', 'YYYYMMDD')+(tb1.dte_computation_on_data/86400),'MM/DD/YYYY') || ' ' ||
    to_char(to_date(mod  (tb1.dte_computation_on_data,86400) ,'sssss'),'hh24:mi:ss ') revert_test,
    systimestamp,tb1.dte_computation_on_data
    from
    ( SELECT -- THIS IS THE FORMULA OF THE OLD FUNCTION THEY USE TO CONVERT DATE TO NUMBER AND INSERTED ON THE ROW
        floor((CAST(SYS_EXTRACT_UTC(systimestamp) AS DATE) - TO_DATE('19700101', 'YYYYMMDD')) * 86400) dte_computation_on_data
      FROM dual)tb1;
     
    results
    ---------------------------------------------------------------------------------------
    REVERT_TEST             SYSTIMESTAMP                            DTE_COMPUTATION_ON_DATA
    05/19/2011 03:46:18     5/19/2011 11:46:18.005171 AM +08:00     1305776778
     
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It's not off, look at the result from systimestamp. SYSTIMESTAMP is defined as a TIMESTAMP WITH TIME ZONE type so it's adding the timezone offset to the original hour (3 am + 8 hour offset = 11 AM).
     
  3. luffy

    luffy Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    i see, hehehe thank you david. nice to meet experts like you who are willing to help us newbies. ;)
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You're welcome, and I understand that at first examination all you saw was the time. It helps to know how such values (systimestamp) are defined so the results can be properly interpreted.