在 sqlplus / Oracle 中将纪元转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3820179/
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 epoch to date in sqlplus / Oracle
提问by Oink
I have the following table:
我有下表：
SQL> desc recording
Name Null? Type
-------------------- -------- ------
CAPTUREID NOT NULL NUMBER(9)
STARTDATE NOT NULL DATE
ENDDATE DATE
STATE NUMBER(1)
ESTIMATEDENDTIME NUMBER(13)
Here's a single line for this table:
这是该表的一行：
SQL> select * from recording where CAPTUREID=14760457;
CAPTUREID STARTDATE ENDDATE STATE ESTIMATEDENDTIME
---------- ------------------- ------------------- ----- ----------------
14760457 29/09/2010 08:50:01 29/09/2010 09:52:04 0 1285746720000
I'm pretty sure that this has been asked so many times before, but all the solutions I've found so far didn't really work, so... How do I convert ESTIMATEDENDTIMEfrom its original epoch form to a DD/MM/YYY HH:MI:SSformat in a single query in SQLPLUS?
我很确定这之前已经被问过很多次了，但是到目前为止我发现的所有解决方案都没有真正起作用，所以......我如何将ESTIMATEDENDTIME其原始的纪元形式DD/MM/YYY HH:MI:SS转换为单个格式在 SQLPLUS 中查询？
Thanks!
谢谢！
回答by Chris Cameron-Mills
In Oracle, adding X to a DATE will return you a DATE X days later.
在 Oracle 中，将 X 添加到 DATE 将在 X 天后返回 DATE。
If ESTIMATEDENDTIME is milliseconds since Epoch then you could do
如果 ESTIMATEDENDTIME 是自 Epoch 以来的毫秒数，那么你可以这样做
DATE '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * ESTIMATEDENDTIME
and then use to_char to achieve the correct format of the resulting date. e.g:
然后使用 to_char 实现结果日期的正确格式。例如：
SELECT
captureid
, startdate
, enddate
, state
, estimatedendtime
, DATE '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * estimatedendtime AS estimatedenddate
FROM recording
回答by UserszrKs
select ((timestamp_coloum_name - to_date('01-JAN-1970','DD-MON-YYYY')) * (86400)) from any_table;
