Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2008
    Location
    Québec City, Canada
    Posts
    10

    Unanswered: UTC timestamps in milliseconds offset from January 1, 1970

    Hello,

    how do I can, from a given datetime, get UTC timestamps in milliseconds offset from January 1, 1970?

    I'm in the Canada/Eastern time zone and I'm using the following function:
    Code:
    60 * 60 * 24 * 1000 * (NEW_TIME (SYSDATE, 'EDT', 'GMT') - TO_DATE ('1970-01-01', 'YYYY-MM-DD'));
    So, we are currently in the Daylight saving Time, but when the time change to Standard time will arise, this code will cease to work.

    Is my current calculation accurate? Do I need to apply kind of NEW_TIME on the 1970-01-01 to be ok?
    How can I get this completely transparent?

    My revert calculation from seconds to date with this UTC timestamp is:
    Code:
    NEW_TIME (TO_DATE ('1970-01-01', 'YYYY-MM-DD') + fl / (24 * 3600 * 1000), 'GMT', 'EDT');
    I want to be accurate with this reverting calculation too... Here too, do I need an UTC convert on TO_DATE ('1970-01-01', 'YYYY-MM-DD') ?

    Thanks

  2. #2
    Join Date
    Jul 2008
    Location
    Québec City, Canada
    Posts
    10

    Question

    ho,

    I followed the following steps and got what I need in an multiple step way:

    Code:
        v_dh_local    DATE  := TO_DATE('2008-01-01 12:30', 'YYYY-MM-DD HH24:MI');
        v_dh_utc      DATE  := v_dh_local AT TIME ZONE 'UTC';
        v_dh_utc_fl   FLOAT := 60 * 60 * 24 * 1000 * (v_dh_utc - TO_DATE ('1970-01-01', 'YYYY-MM-DD'));

    does it seems ok?

    tks

Posting Permissions

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