PL / SQL sysdate为以ms为单位的Unix纪元时间(PL/SQL sysdate to Unix epoch time in ms)

PL / SQL sysdate为以ms为单位的Unix纪元时间(PL/SQL sysdate to Unix epoch time in ms)

我有一堆Oracle sysdate值需要转换为以ms为单位的Unix纪元时间。

例如，具有值的变量

15-MAR-13

应转换为

1363351108398

在PL / SQL中

怎么会那样做？

I have bunch of Oracle sysdate values which need to be converted to Unix epoch time in ms.

For example variable that has value

15-MAR-13

should convert to

1363351108398

in PL/SQL

How would one do that ?

最满意答案

您可以使用此功能。 它还考虑时区，因为Unix epoche是1970-01-01 00:00:00 UTC！

CREATE OR REPLACE FUNCTION GetEpoche(theTimestamp IN TIMESTAMP, timezone IN VARCHAR2 DEFAULT SESSIONTIMEZONE) RETURN NUMBER DETERMINISTIC IS timestampUTC TIMESTAMP; theInterval INTERVAL DAY(9) TO SECOND; epoche NUMBER; BEGIN timestampUTC := FROM_TZ(theTimestamp, timezone) AT TIME ZONE 'UTC'; theInterval := TO_DSINTERVAL(timestampUTC - TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') ); epoche := EXTRACT(DAY FROM theInterval)*24*60*60 + EXTRACT(HOUR FROM theInterval)*60*60 + EXTRACT(MINUTE FROM theInterval)*60 + EXTRACT(SECOND FROM theInterval); RETURN ROUND(1000*epoche); END GetEpoche;

You can use this function. It also considers the time zone, because Unix epoche is 1970-01-01 00:00:00 UTC!

CREATE OR REPLACE FUNCTION GetEpoche(theTimestamp IN TIMESTAMP, timezone IN VARCHAR2 DEFAULT SESSIONTIMEZONE) RETURN NUMBER DETERMINISTIC IS timestampUTC TIMESTAMP; theInterval INTERVAL DAY(9) TO SECOND; epoche NUMBER; BEGIN timestampUTC := FROM_TZ(theTimestamp, timezone) AT TIME ZONE 'UTC'; theInterval := TO_DSINTERVAL(timestampUTC - TO_TIMESTAMP('1970-01-01', 'YYYY-MM-DD') ); epoche := EXTRACT(DAY FROM theInterval)*24*60*60 + EXTRACT(HOUR FROM theInterval)*60*60 + EXTRACT(MINUTE FROM theInterval)*60 + EXTRACT(SECOND FROM theInterval); RETURN ROUND(1000*epoche); END GetEpoche;