oracle 时间戳到数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26649937/
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
Timestamp to number
提问by corvallo
How to convert the CURRENT_TIMESTAMP function in a NUMBER using ORACLE?
如何使用 ORACLE 在 NUMBER 中转换 CURRENT_TIMESTAMP 函数？
Using the following code, I'm able to extract the CURRENT_TIMESTAMP of the system
使用以下代码，我可以提取系统的 CURRENT_TIMESTAMP
SELECT SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) FROM DUAL;
What I want is to use the result of the above code to create the corresponding timestamp in unix format. Pratically: 1) with SELECT SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) FROM DUAL; i receive this result:
我想要的是用上面代码的结果来创建对应的unix格式的时间戳。实际上：1) 使用 SELECT SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) FROM DUAL; 我收到这个结果：
--------------------------------------------------
| SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) |
--------------------------------------------------
| 30-OTT-14 09:51:43,164232000 |
--------------------------------------------------
2) And I want to convert it to:
2）我想将其转换为：
--------------------------------------------------
| UNIXTIMESTAMP |
--------------------------------------------------
| 1414662703 |
--------------------------------------------------
Is that possible? Thank you all in advance
那可能吗？谢谢大家
回答by Multisync
if you need to work with timestamps than you may try this
如果您需要使用时间戳，则可以尝试此操作
select trunc(
extract(day from intvl) * 24 * 60 * 60
+ extract(hour from intvl) * 60 * 60
+ extract(minute from intvl) * 60
+ extract(second from intvl)
)
from
(select SYS_EXTRACT_UTC(CURRENT_TIMESTAMP) - to_timestamp('19700101', 'YYYYMMDD') intvl from dual);
The difference between two timestamps is an interval
两个时间戳之间的差异是一个间隔
EXTRACT(fmt FROM INTERVAL) extracts date time component from an INTERVAL
EXTRACT(fmt FROM INTERVAL) 从 INTERVAL 中提取日期时间分量
TRUNC truncates milliseconds
TRUNC 截断毫秒
