Oracle是否等效于Java System.currentTimeMillis()? [英] Oracle equaivalent of java System.currentTimeMillis()?

问题描述

我希望能够将当前时间(以毫秒为单位)存储在Oracle number字段中.如何通过查询做到这一点?

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?

select systimestamp from dual; 

返回实际时间戳.无论如何，我可以像Java的System.currentTimeMillis()一样将其转换为毫秒吗?

returns the actual timestamp. Is there anyway that I can convert this into milliseconds the same way Java's System.currentTimeMillis() does?

推荐答案

Java函数返回自固定时间以来经过的毫秒数.该时间是UTC 1970年第一天的午夜，即Unix时钟时间的开始.

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.

以下功能对PL/SQL相同.它从起点(其中ms = 1)减去当前时间戳.它提取各种时间分量并将其转换为秒.最后，它将所有内容乘以1000，以毫秒为单位获取值:

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:

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;
/

如果在数据库中启用了Java，则可能会发现创建Java存储过程更简单:

If you have Java enabled in the database you may find it simpler to create a Java Stored Procedure instead:

create or replace function currentTimeMillis return number as
language java name 'java.lang.System.currentTimeMillis() return java.lang.Integer';
/

两种方法的比较:

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>

(我要感谢Simon Nickerson，他在我的PL/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.)

顺便说一句，如果您只对最接近的毫秒数感兴趣，那么Oracle对此具有内置功能:

Incidentally, if you are only interested in time to the nearest centisecond, Oracle has a built-in for that: DBMS_UTILITY.GET_TIME().

这篇关于Oracle是否等效于Java System.currentTimeMillis()?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！