Oracle sql如何把毫秒转为日期,sql – 将毫秒转换为时间戳

如果向时间戳添加间隔,则会获得时间戳(请参阅

date/interval arithmetics).

作为Benoit noticed,当超过大约2.1e9时,您不能指定具有秒的间隔：

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

这就是为什么你应该使用不会失去精度的分钟.例如,假设：TS是unix时间戳(即数字)：

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