在 sqlplus/Oracle 中将纪元转换为日期
我有下表:
SQL> desc recording
Name Null? Type
-------------------- -------- ------
CAPTUREID NOT NULL NUMBER(9)
STARTDATE NOT NULL DATE
ENDDATE DATE
STATE NUMBER(1)
ESTIMATEDENDTIME NUMBER(13)
这是该表的一行:
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
我很确定这之前已经被问过很多次了，但是到目前为止我发现的所有解决方案都没有真正起作用，所以......我如何转换 ESTIMATEDENDTIME在 SQLPLUS 中的单个查询中从其原始时代形式转换为 DD/MM/YYY HH:MI:SS 格式?
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 ESTIMATEDENDTIME from its original epoch form to a DD/MM/YYY HH:MI:SS format in a single query in SQLPLUS?
谢谢！
推荐答案
在 Oracle 中，将 X 添加到 DATE 将在 X 天后返回 DATE.
In Oracle, adding X to a DATE will return you a DATE X days later.
如果 ESTIMATEDENDTIME 是自 Epoch 以来的毫秒数，那么您可以这样做
If ESTIMATEDENDTIME is milliseconds since Epoch then you could do
DATE '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * ESTIMATEDENDTIME
然后使用to_char来实现结果日期的正确格式.例如:
and then use to_char to achieve the correct format of the resulting date. e.g:
SELECT
captureid
, startdate
, enddate
, state
, estimatedendtime
, DATE '1970-01-01' + ( 1 / 24 / 60 / 60 / 1000) * estimatedendtime AS estimatedenddate
FROM recording
相关文章