Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Posts
    148

    Unanswered: Convert time from GMT

    Hi all..We have a DB here that uses this as a time format (god knows why)

    Difference of the current date and time 1/1/1979 in seconds in GMT minus 5.

    I know, crazy...I did not design this...As there a way for me to convert this on the query line? I searched for a data function that could accomplish this and I have the feeling there is one but I just can't seem to dig it up.

  2. #2
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    I can't understand exactly what's date format in your database, but for dealing with GMT dates I've written a function once, it converts date to GMT - maybe this would help you in coding yours?
    Code:
    CREATE OR REPLACE FUNCTION GMT_Date (
      p_add_to DATE,
      p_fmt VARCHAR2 DEFAULT 'mm/dd/yyyy hh24:mi:ss'
    )
    RETURN DATE
    AS
    BEGIN
      RETURN TO_DATE(TO_CHAR(SYS_EXTRACT_UTC(TO_TIMESTAMP_TZ(TO_CHAR(GMT_Date.p_add_to, GMT_Date.p_fmt) || ' ' || TO_CHAR(DBTIMEZONE), GMT_Date.p_fmt || ' tzh:tzm')), 'DD-MON-YYYY HH24:MI:SS'), 'DD-MON-YYYY HH24:MI:SS');
    END GMT_Date;
    /

  3. #3
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    This might also be useful

    (to_date('01011979','ddmmyyyy')+(timeval/86400)+(5/24))

    adds timeval (your seconds since 1/1/79) and then adds 5 hours. If this isnt quite right you can modify it quite easily. One minor point it isnt since 1/1/1970 is it as this quite commonly used a number of operating systems.

    Alan

Posting Permissions

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