6 Replies Latest reply: Jul 31, 2008 7:56 AM by Solomon Yakobson RSS

    How to convert epoch time stamp to timestamp.

    user575682
      Hi,
      I need a help regarding how to convert the epoch Time stamp, to oracle date and time format.

      for example,
      for epoch time stamp:1204104116656

      the time stamp should be :2008-02-13 12:43:00.351

      Please help me on the same
        • 1. Re: How to convert epoch time stamp to timestamp.
          Solomon Yakobson
          I am not sure how did you come up with 2008-02-13 12:43:00.351. AFAIK, UNIX epoch time is January 1, 1970:
          SQL> select timestamp '1970-01-01 00:00:00' + numtodsinterval(1204104116656/1000,'second') from dual;
          
          TIMESTAMP'1970-01-0100:00:00'+NUMTODSINTERVAL(1204104116656/1000,'SECOND')
          ---------------------------------------------------------------------------
          27-FEB-08 09.21.56.656000000 AM
          
          SQL> 
          SY.
          • 2. Re: How to convert epoch time stamp to timestamp.
            646947
            Apparently, something very important to him happened earlier than that time.
            SQL> SELECT
              2     TO_CHAR
              3     (
              4       TO_TIMESTAMP
              5       (
              6        '19691218032103695',
              7        'YYYYMMDDHH24MISSFF3'
              8       ) + NUMTODSINTERVAL(1204104116656 / 1000, 'SECOND'),
              9      'YYYY-MM-DD HH24:MI:SSxFF3'
            10     )
            11  FROM
            12     Dual;

            TO_CHAR(TO_TIMESTAMP('1969121
            -----------------------------
            2008-02-13 12:43:00.351
            • 3. Re: How to convert epoch time stamp to timestamp.
              Sven W.
              http://en.wikipedia.org/wiki/Epoch_%28reference_date%29#Computing

              Which epoch time do you refer to?
              • 4. Re: How to convert epoch time stamp to timestamp.
                646947
                Perhaps he's not on the Gregorian Calendar yet. :)
                • 5. Re: How to convert epoch time stamp to timestamp.
                  user575682
                  So how we can convert epoch to a specific time zone. I am seeing a 5 hour differnce when I tried converting with java.

                  null
                  • 6. Re: How to convert epoch time stamp to timestamp.
                    Solomon Yakobson
                    SQL> select timestamp '1970-01-01 00:00:00' + numtodsinterval(1204104116656/1000,'second') from dual;
                    
                    TIMESTAMP'1970-01-0100:00:00'+NUMTODSINTERVAL(1204104116656/1000,'SECOND')
                    ---------------------------------------------------------------------------
                    27-FEB-08 09.21.56.656000000 AM
                    
                    SQL> select (timestamp '1970-01-01 00:00:00' + numtodsinterval(1204104116656/1000,'second')) at time zone tz_offset('EST') from dual;
                    
                    (TIMESTAMP'1970-01-0100:00:00'+NUMTODSINTERVAL(1204104116656/1000,'SECOND')
                    ---------------------------------------------------------------------------
                    27-FEB-08 08.21.56.656000000 AM -05:00
                    
                    SQL> select (timestamp '1970-01-01 00:00:00' + numtodsinterval(1204104116656/1000,'second')) at time zone tz_offset('PST') from dual;
                    
                    (TIMESTAMP'1970-01-0100:00:00'+NUMTODSINTERVAL(1204104116656/1000,'SECOND')
                    ---------------------------------------------------------------------------
                    27-FEB-08 06.21.56.656000000 AM -07:00
                    
                    SQL> select (timestamp '1970-01-01 00:00:00' + numtodsinterval(1204104116656/1000,'second')) at time zone tz_offset('Europe/Berlin') from dual;
                    
                    (TIMESTAMP'1970-01-0100:00:00'+NUMTODSINTERVAL(1204104116656/1000,'SECOND')
                    ---------------------------------------------------------------------------
                    27-FEB-08 03.21.56.656000000 PM +02:00
                    
                    SQL> select (timestamp '1970-01-01 00:00:00' + numtodsinterval(1204104116656/1000,'second')) at time zone tz_offset('Canada/Mountain') from dual;
                    
                    (TIMESTAMP'1970-01-0100:00:00'+NUMTODSINTERVAL(1204104116656/1000,'SECOND')
                    ---------------------------------------------------------------------------
                    27-FEB-08 07.21.56.656000000 AM -06:00
                    SY.