将毫秒转换为时间戳

Question

我知道将Unix时间戳（以毫秒为单位）转换成SQL时间戳可以使用以下方法：

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

但我需要一个时间戳，所以我尝试使用以下代码：

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

出现了以下错误：

错误：ORA-01841：(完整的)年必须介于-4713和+9999之间，而不能为0

似乎将时间戳加1总是转换为一天。

我该如何获得真正的时间戳？

- radlan

---

相关：http://stackoverflow.com/questions/9483465/oracle-convert-the-long-millisecond-into-date - AlikElzin-kilaka

Answer 1

如果您将一个“时间戳”加上一个“时间间隔”(请参见date/interval arithmetics)，则会得到一个“时间戳”。

正如 Benoit注意到的那样，当秒数超过约 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

- Vincent Malgrat

---

谢谢。这很好用。特别感谢提供的链接，非常有帮助。 - radlan

@radlan 在处理2038年后的日期时，请使用MINUTE，请查看我的更新 ;) - Vincent Malgrat

Answer 2

有两种类型：

• 时间戳
• 时间间隔

时间间隔是通过减去时间戳得到的，将时间戳相加是没有意义的。

如果您需要获取毫秒级的时间间隔，建议使用秒级时间间隔并将其除以1000：

我可以建议：

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

这里的问题在于你不能在同一个时间戳文字中使用超过9个数字。

如果你需要在纪元后添加2,061,464,797,255毫秒，我建议：

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

你得到的是2035年04月29日13点06分37.255000000秒。

看起来它似乎受到了2038年问题的影响：TIMESTAMP'1970-01-01 00:00:00'加上30亿秒无法正常工作，而20亿却可以。

- Benoit

---

当函数numtodsinterval没有这种限制时，为什么要像这样使用静态间隔？ - Vincent Malgrat

@Vincent Malgrat: 你的方法更好，我的答案只是介绍了区间字面量。请注意，timestamp'1970-01-01 00:00:00' + numtodsinterval(3000000000,'SECOND')也存在2038年问题。 - Benoit

也感谢你的帮助，但我更喜欢文森特的解决方案。numtodsinterval函数非常有用。 - radlan

Answer 3

我在这里发布了一些将纳秒转换为时间戳和将时间戳转换为纳秒的方法。这些方法不受时区影响，具有纳秒精度。

你只需要将它调整为使用毫秒而不是纳秒即可。

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

- Danton Estevam Pinto

Answer 4

使用

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

- Rachcha

---

那有什么用呢？我需要对它进行计算。但是当我加上一个数字时，它会被转换为日期（这将丢失时间戳信息）。 - radlan

稍等一下。当你在代码中添加:timestamp时，存在一个基本错误。请给我一点时间。 - Rachcha