database oracle systimestamp (sysdate) 到毫秒
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7147982/
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 systimestamp (sysdate) to milliseconds
提问by Mykhaylo Adamovych
Could you provide implementation of stored function to get current systimestampas milliseconds.
Something I can use like
您能否提供存储函数的实现以获取当前systimestamp毫秒数。
我可以使用的东西
select current_time_ms from dual;
and get the difference, measured in milliseconds, between the current time and midnight, January 1, 1970 UTC.
并获取当前时间与 UTC 1970 年 1 月 1 日午夜之间的差值（以毫秒为单位）。
Thanks.
谢谢。
回答by Mykhaylo Adamovych
- DB timezone agnostic
- with milliseconds
- works in XE
- 数据库时区不可知
- 以毫秒为单位
- 在 XE 工作
function current_time_ms
return number
is
out_result number;
begin
select extract(day from(sys_extract_utc(systimestamp) - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000
+ to_number(to_char(sys_extract_utc(systimestamp), 'SSSSSFF3'))
into out_result
from dual;
return out_result;
end current_time_ms;
回答by Codo
The best thing I know of is:
我所知道的最好的事情是：
select extract(day from (systimestamp - timestamp '1970-01-01 00:00:00')) * 86400000
+ extract(hour from (systimestamp - timestamp '1970-01-01 00:00:00')) * 3600000
+ extract(minute from (systimestamp - timestamp '1970-01-01 00:00:00')) * 60000
+ extract(second from (systimestamp - timestamp '1970-01-01 00:00:00')) * 1000 unix_time
from dual;
I'm not quite sure what requirements you have regarding time zone. You might need to make minor adjustments for that.
我不太确定您对时区有什么要求。您可能需要为此做一些细微的调整。
回答by Piotr Findeisen
Adding to @Mykhaylo Adamovych answer (which looks correct!) here goes a more straightforward approach using oracle Java support (i.e. not in XE and not in AWS RDS). Less portable (in case you care), but seemed faster in mytesting.
添加到@Mykhaylo Adamovych 的答案（看起来是正确的！）这里是使用 oracle Java 支持（即不在 XE 中也不在 AWS RDS 中）的更直接的方法。便携性较差（以防万一），但在我的测试中似乎更快。
CREATE or replace FUNCTION current_java_timestamp RETURN number
AS LANGUAGE JAVA NAME 'java.lang.System.currentTimeMillis() return java.lang.Long';
/
回答by L0uL0u
SELECT to_char(sysdate, 'HH24:MI:SS'), to_char(systimestamp, 'HH24:MI:SS.FF6') FROM dual
回答by Ollie
AFAIK, there is no direct way for achieving this (other than manually writing a long-winded SQL function).
AFAIK，没有直接的方法可以实现这一点（除了手动编写冗长的 SQL 函数）。
Why do you need this specifically?
你为什么特别需要这个？
You could use a stored Java function and then use the System.getCurrentMillis() that Java provides to return you a value in Milliseconds from 1.1.1970 to now.
您可以使用存储的 Java 函数，然后使用 Java 提供的 System.getCurrentMillis() 返回一个从 1970 年 1 月至今的毫秒值。
回答by Nishanthi Grashia
Below code gives the difference in milliseconds:
下面的代码给出了以毫秒为单位的差异：
with t as (select systimestamp - to_timestamp(sysdate ) diff from dual)
select extract(day from diff) * 24 * 3600000+
extract(hour from diff) * 3600000+
extract(minute from diff) * 60000 +
extract(second from diff) * 1000
dif
from t
For conversion of milliseconds to Hours, Minutes, seconds, modify and use below query as appropriate:
要将毫秒转换为小时、分钟、秒，请根据需要修改和使用以下查询：
with t as (select systimestamp - to_timestamp(sysdate ) diff from dual)
select extract(day from diff) * 24 * 3600000+
extract(hour from diff) * 3600000+
extract(minute from diff) * 60000 +
extract(second from diff) * 1000
dif,
(to_char (to_date(round(( extract(day from diff) * 24 * 3600000+
extract(hour from diff) * 3600000+
extract(minute from diff) * 60000 +
extract(second from diff) * 1000)/1000), 'SSSSS' ), 'HH24"Hrs" MI"Min" SS"Sec"')) timeval
from t
