SQL Oracle中以毫秒为单位的时间戳之间的差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22606161/
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
Difference Between Timestamps in Milliseconds in Oracle
提问by Ashok Kumar Dabbadi
I have table test1 and have one column DOJ with timestamp datatype having few records as shown below. Here i need the difference im milliseconds between doj and systimestamp.
我有表 test1 并且有一个带有时间戳数据类型的列 DOJ，其记录很少，如下所示。这里我需要 doj 和 systimestamp 之间的毫秒差异。
SELECT DOJ FROM TEST1;
DOJ
----------------------------
21-MAR-14 09.25.34.514526000
21-MAR-14 09.25.34.520345000
22-MAR-14 09.25.34.523144000
22-MAR-14 09.25.34.527770000
23-MAR-14 09.25.34.532482000
23-MAR-14 09.25.34.535603000
24-MAR-14 09.25.34.538556000
24-MAR-14 09.25.34.541729000
SELECT SYSTIMESTAMP FROM DUAL;
SYSTIMESTAMP
--------------
24-MAR-14 09.48.10.251556000 +00:00
Can some one please help me with this.
有人可以帮我解决这个问题。
采纳答案by René Nyffenegger
select
extract(second from systimestamp - doj) * 1000
from
test1;
回答by Alex Poole
Expanding René's answer a bit, if you want the total milliseconds then you need to extract and combine all of the elements from the interval that's produced by subtracting one timestamp from another:
稍微扩展 René 的答案，如果您想要总毫秒数，那么您需要从通过从另一个时间戳中减去一个时间戳产生的间隔中提取和组合所有元素：
select doj, systimestamp - doj,
trunc(1000 * (extract(second from systimestamp - doj)
+ 60 * (extract(minute from systimestamp - doj)
+ 60 * (extract(hour from systimestamp - doj)
+ 24 * (extract(day from systimestamp - doj) ))))) as milliseconds
from test1;
DOJ SYSTIMESTAMP-DOJ MILLISECONDS
---------------------------- ---------------- ----------------
21-MAR-14 09.25.34.514526000 3 2:9:8.785713 266948785
21-MAR-14 09.25.34.520345000 3 2:9:8.779894 266948779
22-MAR-14 09.25.34.523144000 2 2:9:8.777095 180548777
22-MAR-14 09.25.34.527770000 2 2:9:8.772469 180548772
23-MAR-14 09.25.34.532482000 1 2:9:8.767757 94148767
23-MAR-14 09.25.34.535603000 1 2:9:8.764636 94148764
24-MAR-14 09.25.34.538556000 0 2:9:8.761683 7748761
24-MAR-14 09.25.34.541729000 0 2:9:8.75851 7748758
SQL Fiddle, including the Unix epoch date for comparison, though you'd need to adjust that for your server time zone.
SQL Fiddle，包括用于比较的 Unix 纪元日期，但您需要针对您的服务器时区进行调整。
回答by MT0
If you need to handle leap secondsthen you can create a utility package that will adjust the epoch time to account for this:
如果您需要处理闰秒，那么您可以创建一个实用程序包来调整纪元时间以解决此问题：
CREATE OR REPLACE PACKAGE time_utils
IS
FUNCTION milliseconds_since_epoch(
in_datetime IN TIMESTAMP,
in_epoch IN TIMESTAMP DEFAULT TIMESTAMP '1970-01-01 00:00:00'
) RETURN NUMBER;
FUNCTION milliseconds_epoch_to_ts (
in_milliseconds IN NUMBER,
in_epoch IN TIMESTAMP DEFAULT TIMESTAMP '1970-01-01 00:00:00'
) RETURN TIMESTAMP;
END;
/
SHOW ERRORS;
CREATE OR REPLACE PACKAGE BODY time_utils
IS
-- List of the seconds immediately following leap seconds:
leap_seconds CONSTANT SYS.ODCIDATELIST := SYS.ODCIDATELIST(
DATE '1972-07-01',
DATE '1973-01-01',
DATE '1974-01-01',
DATE '1975-01-01',
DATE '1976-01-01',
DATE '1977-01-01',
DATE '1978-01-01',
DATE '1979-01-01',
DATE '1980-01-01',
DATE '1981-07-01',
DATE '1982-07-01',
DATE '1983-07-01',
DATE '1985-07-01',
DATE '1988-01-01',
DATE '1990-01-01',
DATE '1991-01-01',
DATE '1992-07-01',
DATE '1993-07-01',
DATE '1994-07-01',
DATE '1996-01-01',
DATE '1997-07-01',
DATE '1999-01-01',
DATE '2006-01-01',
DATE '2009-01-01',
DATE '2012-07-01',
DATE '2015-07-01',
DATE '2016-01-01'
);
HOURS_PER_DAY CONSTANT BINARY_INTEGER := 24;
MINUTES_PER_HOUR CONSTANT BINARY_INTEGER := 60;
SECONDS_PER_MINUTE CONSTANT BINARY_INTEGER := 60;
MILLISECONDS_PER_SECOND CONSTANT BINARY_INTEGER := 1000;
MINUTES_PER_DAY CONSTANT BINARY_INTEGER := HOURS_PER_DAY * MINUTES_PER_HOUR;
SECONDS_PER_DAY CONSTANT BINARY_INTEGER := MINUTES_PER_DAY * SECONDS_PER_MINUTE;
MILLISECONDS_PER_MINUTE CONSTANT BINARY_INTEGER := SECONDS_PER_MINUTE * MILLISECONDS_PER_SECOND;
MILLISECONDS_PER_HOUR CONSTANT BINARY_INTEGER := MINUTES_PER_HOUR * MILLISECONDS_PER_MINUTE;
MILLISECONDS_PER_DAY CONSTANT BINARY_INTEGER := HOURS_PER_DAY * MILLISECONDS_PER_HOUR;
FUNCTION milliseconds_since_epoch(
in_datetime IN TIMESTAMP,
in_epoch IN TIMESTAMP DEFAULT TIMESTAMP '1970-01-01 00:00:00'
) RETURN NUMBER
IS
p_leap_milliseconds BINARY_INTEGER := 0;
p_diff INTERVAL DAY(9) TO SECOND(3);
BEGIN
IF in_datetime IS NULL OR in_epoch IS NULL THEN
RETURN NULL;
END IF;
p_diff := in_datetime - in_epoch;
IF in_datetime >= in_epoch THEN
FOR i IN 1 .. leap_seconds.COUNT LOOP
EXIT WHEN in_datetime < leap_seconds(i);
IF in_epoch < leap_seconds(i) THEN
p_leap_milliseconds := p_leap_milliseconds + MILLISECONDS_PER_SECOND;
END IF;
END LOOP;
ELSE
FOR i IN REVERSE 1 .. leap_seconds.COUNT LOOP
EXIT WHEN in_datetime > leap_seconds(i);
IF in_epoch > leap_seconds(i) THEN
p_leap_milliseconds := p_leap_milliseconds - MILLISECONDS_PER_SECOND;
END IF;
END LOOP;
END IF;
RETURN MILLISECONDS_PER_SECOND * EXTRACT( SECOND FROM p_diff )
+ MILLISECONDS_PER_MINUTE * EXTRACT( MINUTE FROM p_diff )
+ MILLISECONDS_PER_HOUR * EXTRACT( HOUR FROM p_diff )
+ MILLISECONDS_PER_DAY * EXTRACT( DAY FROM p_diff )
+ p_leap_milliseconds;
END milliseconds_since_epoch;
FUNCTION milliseconds_epoch_to_ts(
in_milliseconds IN NUMBER,
in_epoch IN TIMESTAMP DEFAULT TIMESTAMP '1970-01-01 00:00:00'
) RETURN TIMESTAMP
IS
p_datetime TIMESTAMP;
BEGIN
IF in_milliseconds IS NULL OR in_epoch IS NULL THEN
RETURN NULL;
END IF;
p_datetime := in_epoch
+ NUMTODSINTERVAL( in_milliseconds / MILLISECONDS_PER_SECOND, 'SECOND' );
IF p_datetime >= in_epoch THEN
FOR i IN 1 .. leap_seconds.COUNT LOOP
EXIT WHEN p_datetime < leap_seconds(i);
IF in_epoch < leap_seconds(i) THEN
p_datetime := p_datetime - INTERVAL '1' SECOND;
END IF;
END LOOP;
ELSE
FOR i IN REVERSE 1 .. leap_seconds.COUNT LOOP
EXIT WHEN p_datetime > leap_seconds(i);
IF in_epoch > leap_seconds(i) THEN
p_datetime := p_datetime + INTERVAL '1' SECOND;
END IF;
END LOOP;
END IF;
RETURN p_datetime;
END milliseconds_epoch_to_ts;
END;
/
SHOW ERRORS;
Then you can do:
然后你可以这样做：
SELECT TIME_UTILS.MILLISECONDS_SINCE_EPOCH(
in_datetime => TIMESTAMP '1974-01-01 00:00:00.000',
in_epoch => TIMESTAMP '1973-12-31 23:59:59.999'
) AS diff
FROM DUAL;
And get the output:
并得到输出：
DIFF
----
1001
Note: you will need to keep the package up-to-date when new leap-seconds are proposed.
注意：当提出新的闰秒时，您需要使包保持最新。
