oracle sysdate毫秒,oracle systimestamp（sysdate）到毫秒

7 个答案:

答案 0 :(得分：18)

DB timezone agnostic

毫秒

适用于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;

答案 1 :(得分：7)

我所知道的最好的事情是：

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;

我不太确定您对时区有什么要求。您可能需要对此进行微调。

答案 2 :(得分：4)

添加到@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';

/

答案 3 :(得分：1)

下面的代码给出了以毫秒为单位的差异：

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

将毫秒转换为小时，分钟，秒，根据需要修改和使用以下查询：

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

答案 4 :(得分：1)

AFAIK，没有直接的方法来实现这一点(除了手动编写冗长的SQL函数)。

为什么你需要这个？

您可以使用存储的Java函数，然后使用Java提供的System.getCurrentMillis()来返回从1.1.1970到现在的毫秒数值。

答案 5 :(得分：0)

我已经发布了here一些将时间戳转换为纳秒和将纳秒转换为时间戳的方法。这些方法不受时区的影响，并且具有纳秒级的精度。

您只需将其调整为毫秒而不是纳秒。

SELECT (EXTRACT(DAY FROM (

SYSTIMESTAMP --Replace line with desired timestamp --Maximum value: TIMESTAMP '3871-04-29 10:39:59.999999999 UTC'

- TIMESTAMP '1970-01-01 00:00:00 UTC') * 24 * 60) * 60 + EXTRACT(SECOND FROM

SYSTIMESTAMP --Replace line with desired timestamp

)) * 1000 AS MILLIS FROM DUAL;

MILLIS

1598434427263.027

答案 6 :(得分：-1)

SELECT to_char(sysdate, 'HH24:MI:SS'), to_char(systimestamp, 'HH24:MI:SS.FF6') FROM dual