Oracle current_timestamp 到秒的转换
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5881235/
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
Oracle current_timestamp to seconds conversion
提问by Poorna
We are using Oracle database.
我们正在使用 Oracle 数据库。
In our table timestamp is stored as seconds since 1970, how can I convert the time stamp obtained through current_timestamp() function to seconds
在我们的表中时间戳存储为自 1970 年以来的秒数，如何将通过 current_timestamp() 函数获得的时间戳转换为秒
回答by Tony Andrews
This would do it:
这样做：
select round((cast(current_timestamp as date) - date '1970-01-01')*24*60*60) from dual
Though I wouldn't use current_timestamp if I was only interested in seconds, I would use SYSDATE:
虽然如果我只对秒感兴趣，我不会使用 current_timestamp，但我会使用 SYSDATE：
select round((SYSDATE - date '1970-01-01')*24*60*60) from dual
回答by Mykhaylo Adamovych
回答by Tagar
Maybe not completely relevant. I had to resolve other way around problem (e.g. Oracle stores timestamp in V$RMAN_STATUS and V$RMAN_OUTPUT) and I had to convert that to date/timestamp. I was surprised, but the magic date is not 1970-01-01 there, but 1987-07-07. I looked at Oracle's history and the closest date I can think of is when they ported Oracle products to UNIX. Is this right?
也许不完全相关。我不得不以其他方式解决问题（例如，Oracle 在 V$RMAN_STATUS 和 V$RMAN_OUTPUT 中存储时间戳），我不得不将其转换为日期/时间戳。我很惊讶，但神奇的日期不是 1970-01-01，而是 1987-07-07。我查看了 Oracle 的历史，我能想到的最接近的日期是他们将 Oracle 产品移植到 UNIX 的时间。这是正确的吗？
Here's my SQL
这是我的 SQL
SELECT /*+ rule */
to_char(min(stamp)/(24*60*60) + date '1987-07-07', 'DD-MON-YYYY HH24:MI:SS') start_tm
, to_char(to_char(max(stamp)/(24*60*60) + date '1987-07-07', 'DD-MON HH24:MI:SS')) end_tm
FROM V$RMAN_STATUS
START WITH (RECID, STAMP) =
(SELECT MAX(session_recid),MAX(session_stamp) FROM V$RMAN_OUTPUT)
CONNECT BY PRIOR RECID = parent_recid ;
回答by Aldis
I needed to send timestamp to GrayLog via GELF from Oracle DB. I tried different versions and solutions but only one worked correctly.
我需要从 Oracle DB 通过 GELF 将时间戳发送到 GrayLog。我尝试了不同的版本和解决方案，但只有一个可以正常工作。
SQL:
查询语句：
SELECT REPLACE((CAST(dat AS DATE) - TO_DATE('19700101', 'YYYYMMDD')) * 86400 + MOD(EXTRACT(SECOND FROM dat), 1), ',', '.') AS millis
FROM (SELECT SYSTIMESTAMP AT TIME ZONE 'GMT' AS dat FROM dual)
The result for Systmiestamp
Sysmiestamp 的结果
2018/12/18 19:47:29,080988 +02:00
2018/12/18 19:47:29,080988 +02:00
will be
将会
1545155249.080988
