SQL 将毫秒转换为时间戳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15520256/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Convert milliseconds to Timestamp
提问by radlan
I know that to convert a Unix timestamp in milliseconds to an SQL timestamp I can use
我知道要将以毫秒为单位的 Unix 时间戳转换为我可以使用的 SQL 时间戳
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
错误：ORA-01841：（完整）年份必须介于 -4713 和 +9999 之间，并且不能为 0
It seems that adding 1 to the timestamp always converts it to a day.
似乎在时间戳上加 1 总是将其转换为一天。
How can I do the same to get a real timestamp?
我怎样才能得到一个真正的时间戳？
采纳答案by Vincent Malgrat
You will get a timestampif you add an intervalto a timestamp(see date/interval arithmetics).
如果将 atimestamp添加interval到 a timestamp（请参阅日期/间隔算术），您将得到 a 。
As Benoit noticed, you can't specify an interval with seconds when there are more than about 2.1e9 of them:
正如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
This is why you should use minutes which do not lose precision. For example, assuming :TSis the unix timestamp (i.e. a number):
这就是为什么你应该使用不会失去精度的分钟。例如，假设: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
回答by Benoit
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:
如果您需要获得毫秒间隔，我建议使用第二个间隔并将其除以 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.
这里的问题是在同一个时间戳文字中不能使用超过 9 位数字。
If you need to ad 2,061,464,797,255 milliseconds to the epoch I can suggest:
如果您需要将 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
You get 2035-04-29 13:06:37.255000000
你得到 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.
它似乎受到 2038 错误的影响：TIMESTAMP'1970-01-01 00:00:00' + 30 亿秒不起作用，而它适用于 20 亿。
回答by Rachcha
Use
用
SELECT TIMESTAMP '1970-01-01 00:00:00.1234' + INTERVAL '1 00:00:00' DAY TO SECOND
AS ts
FROM dual;
