1 Reply Latest reply: Mar 14, 2010 3:14 AM by 704043 RSS

    Oracle date to unix timestamp

    677729
      Hi..

      1. What is the command to convert oracle date to unix timestamp ?
      2. What is the command to convert unix timestamp to oracle date ?

      This is my conversion query, but when i convert back to oracle date in different server the time become late 1 hour. Why this is happened ?... is there any problem on my sessiontimezone or dbtimezone configuration ?

      create or replace FUNCTION oracle_to_unix
      (
      in_date IN DATE)
      RETURN NUMBER
      IS
      BEGIN
      RETURN (in_date -TO_DATE('19700101','yyyymmdd'))*86400 - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600;
      END;


      Please help me ...

      Thank you,
      Baharin
        • 1. Re: Oracle date to unix timestamp
          704043
          Hello !

          If i substract something and then add the same then i should get the same result.

          I have tested like this
          SQL> select sessiontimezone from dual;
          
          SESSIONTIMEZONE
          ---------------------------------------------------------------------------
          +01:00
          
          SQL> select dbtimezone from dual;
          
          DBTIME
          ------
          +02:00
          
          SQL> select (to_date('14.03.2010 08:16:22','dd.mm.yyyy HH24:mi:ss')
            2        - to_date('01.01.1970 00:00:00','dd.mm.yyyy HH24:mi:ss')) * 24 * 60 * 60
            3        - TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600 utim
            4    from dual
            5  /
          
                UTIM
          ----------
          1268550982
          
          SQL> select to_char(to_date('01.01.1970 00:00:00','dd.mm.yyyy HH24:mi:ss') +
            2  (1268550982+TO_NUMBER(SUBSTR(TZ_OFFSET(sessiontimezone),1,3))*3600) / 24 / 60 / 60,
            3  'DD.MM.YYYY HH24:MI:SS')  from dual
            4  /
          
          TO_CHAR(TO_DATE('01
          -------------------
          14.03.2010 08:16:22
          
          SQL> 
          and it seems allright to me .
          Check sessiontimezone at the sessions on both servers. This calculations only depends on sessiontimezones.

          T