oracle - Unix 时间戳到 ISO-8601 字符串

Question

我有一个名为 ACQDATA 的 Oracle 表，其中包含一个 READDATETIME 字段，其中我将 Unix 时间戳(以毫秒为单位)存储为 INTEGER ( NUMBER(38)) 类型。

SQL> select READDATETIME from ACQDATA where ID=1000;

READDATETIME
____________
   1.4793E+12

我需要选择该值作为 ISO-8601 字符串 (YYYY-MM-DDTHH:MM:SS.mmm):

SQL> select READDATETIME from ACQDATA where ID=1000;

READDATETIME
-------------------
   1.4793E+12

我尝试使用TO_CHAR将其转换，但结果很困惑:

   SQL> select TO_CHAR(TO_DATE('1970-01-01','YYYY-MM-DD') + NUMTODSINTERVAL(READDATETIME, 'SECOND'), 'YYYY-MM-DD HH24:MI:SS') from ACQDATA where ID=1000;

Error at line 1:
ORA-01873: the leading precision of the interval is too small    

感谢帮助。

Answer 1

亚历克斯的回答并不完全正确。 Unix 时间戳始终基于 1970-01-01 00:00:00 UTC

除非您的 session 在 UTC 时区运行，否则精确的解决方案将如下所示:

select 
   TO_CHAR((TIMESTAMP '1970-01-01 00:00:00 UTC' + readdatetime/1000 * INTERVAL '1' SECOND) AT LOCAL, 'YYYY-MM-DD"T"HH24:MI:SS.FF3')
from ACQDATA where ID=1000;

或

select 
   TO_CHAR((TIMESTAMP '1970-01-01 00:00:00' AT TIME ZONE 'UTC' + readdatetime/1000 * INTERVAL '1' SECOND) AT LOCAL, 'YYYY-MM-DD"T"HH24:MI:SS.FF3')
from ACQDATA where ID=1000;

或者如果您更喜欢函数而不是文字:

select 
   TO_CHAR((TO_TIMESTAMP_TZ('1970-01-01 00:00:00 UTC', 'YYYY-MM-DD HH24:MI:SS TZR') + numtodsinterval(readdatetime/1000, 'SECOND')) AT LOCAL, 'YYYY-MM-DD"T"HH24:MI:SS.FF3')
from ACQDATA where ID=1000;