Convert milliseconds to Timestamp
Solution 1
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
Solution 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.
Solution 3
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
radlan
Updated on August 27, 2020Comments
-
radlan over 3 years
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?
-
radlan about 11 yearsWhat 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).
-
Rachcha about 11 yearsJust a min. There's a fundamental mistake when you add
:timestamp
in your code. Give me a while -
Vincent Malgrat about 11 yearsWhy would you use static interval like this when the function
numtodsinterval
has no such limitation? -
Benoit about 11 years@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. -
Vincent Malgrat about 11 yearsnice catch! I didn't know about that!
-
radlan about 11 yearsThanks. This works great. And a special thanks for the link, which is very helpful.
-
radlan about 11 yearsThanks to you, too. But I like Vincents solution. The
numtodsinterval
function is quite useful. -
Vincent Malgrat about 11 years@radlan use
MINUTE
when dealing with dates > 2038, see my update ;)