Convert milliseconds to Timestamp

Question

I know that to convert a Unix timestamp in milliseconds to an SQL timestamp I can use

SELECT TO_DATE('1970-01-01','YYYY-MM-DD HH24:MI:SS') + 
       (:timestamp / (1000*60*60*24)) FROM DUAL;

But I need a Timestamp, so I tried with

SELECT TO_TIMESTAMP('1970-01-01 00:00:00','YYYY-MM-DD HH24:MI:SSFF3') + 
       (:timestamp) from DUAL

Which gives me the error:

Error: ORA-01841: (full) year must be between -4713 and +9999, and not be 0

It seems that adding 1 to the timestamp always converts it to a day.

How can I do the same to get a real timestamp?

Related: http://stackoverflow.com/questions/9483465/oracle-convert-the-long-millisecond-into-date — AlikElzin-kilaka, May 31 '16 at 09:44

score 6 · Accepted Answer · edited May 23 '17 at 11:44

You will get a timestamp if you add an interval to a timestamp (see date/interval arithmetics).

As Benoit noticed, you can't specify an interval with seconds when there are more than about 2.1e9 of them:

SQL> SELECT numtodsinterval(2.2e9, 'SECOND'),
  2         numtodsinterval(2.3e9, 'SECOND')
  3    FROM dual;

NUMTODSINTERVAL(2.2E9,'SECOND'  NUMTODSINTERVAL(2.3E9,'SECOND'
------------------------------- -------------------------------
+000024855 03:14:07.147483647   +000024855 03:14:07.147483647

This is why you should use minutes which do not lose precision. For example, assuming :TS is the unix timestamp (i.e. a number):

SQL> variable ts number;
SQL> -- determining unix timestamp with nanosecond precision
SQL> BEGIN
  2     :ts := (to_date('2099-01-01 01:02:03', 'yyyy-mm-dd hh24:mi:ss')
  3              - date '1970-01-01') * 1000*60*60*24
  4            + 123.456789;
  5  END;
  6  /

ts
---------
4070912523123,456789

SQL> select timestamp '1970-01-01 00:00:00'
  2         + numtodsinterval((:ts)/1000/60, 'MINUTE')
  3    from dual;

TIMESTAMP'1970-01-0100:00:00'+NUMTODSINTERVAL((:TS)/1000/60,'MINUTE')
---------------------------------------------------------------------------
2099-01-01 01:02:03.123456789

Thanks. This works great. And a special thanks for the link, which is very helpful. — radlan, Mar 20 '13 at 11:04
@radlan use `MINUTE` when dealing with dates > 2038, see my update ;) — Vincent Malgrat, Mar 20 '13 at 11:27

Benoit · Answer 2 · 2013-03-20T10:21:20.323

2

There are two types:

Timestamps
Intervals

Intervals is what you get when you subtract timestamps, and it is nonsensical to add timestamps together.

If you need to get a millisecond interval, I would suggest to use a second interval and divide it by 1000:

I could suggest:

SELECT timestamp'1970-01-01 00:00:00' + (interval '1888' second(9) / 1000)
  FROM dual

The problem here is that you cannot use more than 9 digits in a same timestamp literal.

If you need to ad 2,061,464,797,255 milliseconds to the epoch I can suggest:

SELECT TIMESTAMP'1970-01-01 00:00:00'
       + INTERVAL '2' SECOND(9) * 1000000000
       + INTERVAL '061464797' SECOND(9)
       + INTERVAL '255' SECOND(3) / 1000
  FROM dual

You get 2035-04-29 13:06:37.255000000

It seems to be subject to the 2038 bug: TIMESTAMP'1970-01-01 00:00:00' + 3 billion seconds does not work, whereas it works with 2 billion.

edited Mar 20 '13 at 10:21

answered Mar 20 '13 at 10:13

Benoit

76,634
23
210
236

Why would you use static interval like this when the function [`numtodsinterval`](http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions117.htm#i88258) has no such limitation? – Vincent Malgrat Mar 20 '13 at 10:42
@Vincent Malgrat: your approach is better, my answer just introduces interval literals. Note that `timestamp'1970-01-01 00:00:00' + numtodsinterval(3000000000,'SECOND')` also has the 2038 bug. – Benoit Mar 20 '13 at 10:53
Thanks to you, too. But I like Vincents solution. The `numtodsinterval` function is quite useful. – radlan Mar 20 '13 at 11:05

Danton Estevam Pinto · Answer 3 · 2020-08-26T13:49:46.360

I've posted here some methods to convert nanoseconds to timestamp and timestamp to nanoseconds. These methods are not affected by time zones and have a nanosecond precision.

You just need to adjust it to use milliseconds instead of nanoseconds.

SELECT TIMESTAMP '1970-01-01 00:00:00 UTC' + numtodsinterval(
    1598434427263 --Replace line with desired milliseconds
/ 1000, 'SECOND') AS TIMESTAMP FROM dual;

TIMESTAMP
26/08/20 09:33:47,263000000 UTC

Rachcha · Answer 4 · 2013-03-20T10:15:31.813

0

Use

SELECT TIMESTAMP '1970-01-01 00:00:00.1234' + INTERVAL '1 00:00:00' DAY TO SECOND  
       AS ts
  FROM dual;

edited Mar 20 '13 at 10:15

answered Mar 20 '13 at 10:03

Rachcha

8,486
8
48
70

What does that help? I need to calculate with it. But when adding a number to it, it is converted to a date (which loses the timestamp information). – radlan Mar 20 '13 at 10:08
Just a min. There's a fundamental mistake when you add `:timestamp` in your code. Give me a while – Rachcha Mar 20 '13 at 10:11

Convert milliseconds to Timestamp

4 Answers4