Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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.

like image 647
Ashok Kumar Dabbadi Avatar asked Mar 24 '14 10:03

Ashok Kumar Dabbadi


2 Answers

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.

like image 109
Alex Poole Avatar answered Sep 25 '22 06:09

Alex Poole


select
  extract(second from systimestamp - doj) * 1000
from
  test1;
like image 38
René Nyffenegger Avatar answered Sep 21 '22 06:09

René Nyffenegger