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
1363351108398in 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;更多推荐
发布评论