Difference Between Timestamps in Milliseconds in Oracle
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.
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.
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:
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.
select
extract(second from systimestamp - doj) * 1000
from
test1;
If you need to handle leap seconds then 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.