Oracle 等价于 java System.currentTimeMillis()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2824710/
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 equaivalent of java System.currentTimeMillis()?
提问by Gaurav
I want to be able to store the current time in milliseconds in an Oracle number field. How do I do this via a query?
我希望能够在 Oracle 数字字段中以毫秒为单位存储当前时间。我如何通过查询做到这一点？
select systimestamp from dual;
returns the actual timestamp. Is there anyway that I can convert this into milliseconds the same way Java's System.currentTimeMillis()does?
返回实际时间戳。无论如何，我可以像JavaSystem.currentTimeMillis()那样将其转换为毫秒吗？
回答by APC
The Java function returns the number of milliseconds which have elapsed since a fixed moment in time. That time is midnight on the first day of 1970 UTC, i.e. the start of Unix clock time.
Java 函数返回自固定时刻以来经过的毫秒数。那个时间是 1970 UTC 第一天的午夜，即 Unix 时钟时间的开始。
The following function does the same for PL/SQL. It subtracts the current timestamp from the starting point (where ms=1). It extracts the various time components and turns them into seconds. Finally it multiplies everything by 1000 to get the value in milliseconds:
以下函数对 PL/SQL 执行相同的操作。它从起点（其中 ms=1）减去当前时间戳。它提取各种时间分量并将它们转换为秒。最后，它将所有内容乘以 1000 以获得以毫秒为单位的值：
create or replace function current_millisecs
return number
is
base_point constant timestamp := to_timestamp('01-JAN-1970 00:00:00.000');
now constant timestamp := systimestamp AT TIME ZONE 'UTC' ;
begin
return (
((extract(day from (now-base_point)))*86400)
+ ((extract(hour from (now-base_point)))*3600)
+ ((extract(minute from (now-base_point)))*60)
+ ((extract(second from (now-base_point))))
) * 1000;
end;
/
If you have Java enabled in the database you may find it simpler to create a Java Stored Procedure instead:
如果您在数据库中启用了 Java，您可能会发现创建 Java 存储过程更简单：
create or replace function currentTimeMillis return number as
language java name 'java.lang.System.currentTimeMillis() return java.lang.Integer';
/
Comparison of the two approaches:
两种方法的比较：
SQL> select currentTimeMillis as JAVA
2 , current_millisecs as PLSQL
3 , currentTimeMillis - current_millisecs as DIFF
4 from dual
5 /
JAVA PLSQL DIFF
---------- ---------- ----------
1.2738E+12 1.2738E+12 0
SQL>
(My thanks go to Simon Nickerson, who spotted the typo in the previous version of my PL/SQL function which produced an anomalous result.)
（我要感谢 Simon Nickerson，他在我的 PL/SQL 函数的先前版本中发现了错误，该错误产生了异常结果。）
Incidentally, if you are only interested in time to the nearest centisecond, Oracle has a built-in for that: DBMS_UTILITY.GET_TIME().
顺便说一句，如果您只对最接近厘米的时间感兴趣，Oracle 有一个内置的：DBMS_UTILITY.GET_TIME()。
回答by Milad
this link helps for all languages currentmillis.comfor oracle:
此链接适用于所有语言 currentmillis.comfor oracle：
SELECT (SYSDATE - TO_DATE('01-01-1970 00:00:00', 'DD-MM-YYYY HH24:MI:SS')) * 24 * 60 * 60 * 1000 FROM DUAL
回答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 Renjith S
Try this -
尝试这个 -
select extract(day from(sys_extract_utc(systimestamp AT TIME ZONE 'GMT') - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000 + to_number(to_char(sys_extract_utc(systimestamp AT TIME ZONE 'GMT'), 'SSSSSFF3')) from dual;
select extract(day from(sys_extract_utc(systimestamp AT TIME ZONE 'GMT') - to_timestamp('1970-01-01', 'YYYY-MM-DD'))) * 86400000 + to_number(to_char(sys_extract_utc(systimestamp AT TIME ZONE) 'GMT'), 'SSSSSFF3')) 来自双；
