When you subtract two variables of type TIMESTAMP, you get an INTERVAL DAY TO SECOND which includes a number of milliseconds and/or microseconds depending on the platform. If the database is running on Windows, systimestamp will generally have milliseconds.
If the database is running on Unix, systimestamp will generally have microseconds.
1 select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' )
2* from dual
SQL> /
SYSTIMESTAMP-TO_TIMESTAMP('2012-07-23','YYYY-MM-DD')
---------------------------------------------------------------------------
+000000000 14:51:04.339000000
You can use the EXTRACT function to extract the individual elements of an INTERVAL DAY TO SECOND
SQL> ed
Wrote file afiedt.buf
select extract( day from diff ) days,
extract( hour from diff ) hours,
extract( minute from diff ) minutes,
extract( second from diff ) seconds
from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff
from dual)
SQL> /
DAYS HOURS MINUTES SECONDS
---------- ---------- ---------- ----------
0 14 55 37.936
You can then convert each of those components into milliseconds and add them up
SQL> ed
Wrote file afiedt.buf
1 select extract( day from diff )*24*60*60*1000 +
2 extract( hour from diff )*60*60*1000 +
3 extract( minute from diff )*60*1000 +
4 round(extract( second from diff )*1000) total_milliseconds
5 from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff
6* from dual)
SQL> /
TOTAL_MILLISECONDS
------------------
53831842
Normally, however, it is more useful to have either the INTERVAL DAY TO SECOND representation or to have separate columns for hours, minutes, seconds, etc. rather than computing the total number of milliseconds between two TIMESTAMP values.
Subtraction between timestamps returns an INTERVAL datatype. You can use the EXTRACT function to return various parts of an interval eg select extract(hour from (timestamp '2009-12-31 14:00:00' - timestamp '2009-12-31 12:15:00')) hr from dual; Note: That only shows the HOUR part, so if the difference is 1 day and 1 hour, this will show 1 not 25. – Gary Myers Jul 8 '09 at 22:42
Another answer:
SQL> @id8
SQL> drop table holder ;
Table dropped.
SQL> create table holder (
2 beg_date timestamp,
3 end_date timestamp)
4 /
Table created.
SQL> INSERT INTO HOLDER VALUES(to_timestamp('2009-07-16:19:00:01.50','YYYY-MM-DD:HH24:MI:SS.FF'),
2 to_timestamp('2009-08-17:20:00','YYYY-MM-DD:HH24:MI'));
1 row created.
SQL> COMMIT;
Commit complete.
EXTRACT (HOUR FROM (END_DATE-BEG_DATE))*60*60+
EXTRACT (MINUTE FROM (END_DATE-BEG_DATE))*60+
EXTRACT (SECOND FROM (END_DATE-BEG_DATE)) DELTA
FROM holder
DELTA
----------
2768398.5
1 select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' )
2* from dual
SQL> /
SYSTIMESTAMP-TO_TIMESTAMP('2012-07-23','YYYY-MM-DD')
---------------------------------------------------------------------------
+000000000 14:51:04.339000000
You can use the EXTRACT function to extract the individual elements of an INTERVAL DAY TO SECOND
SQL> ed
Wrote file afiedt.buf
select extract( day from diff ) days,
extract( hour from diff ) hours,
extract( minute from diff ) minutes,
extract( second from diff ) seconds
from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff
from dual)
SQL> /
DAYS HOURS MINUTES SECONDS
---------- ---------- ---------- ----------
0 14 55 37.936
You can then convert each of those components into milliseconds and add them up
SQL> ed
Wrote file afiedt.buf
1 select extract( day from diff )*24*60*60*1000 +
2 extract( hour from diff )*60*60*1000 +
3 extract( minute from diff )*60*1000 +
4 round(extract( second from diff )*1000) total_milliseconds
5 from (select systimestamp - to_timestamp( '2012-07-23', 'yyyy-mm-dd' ) diff
6* from dual)
SQL> /
TOTAL_MILLISECONDS
------------------
53831842
Normally, however, it is more useful to have either the INTERVAL DAY TO SECOND representation or to have separate columns for hours, minutes, seconds, etc. rather than computing the total number of milliseconds between two TIMESTAMP values.
Subtraction between timestamps returns an INTERVAL datatype. You can use the EXTRACT function to return various parts of an interval eg select extract(hour from (timestamp '2009-12-31 14:00:00' - timestamp '2009-12-31 12:15:00')) hr from dual; Note: That only shows the HOUR part, so if the difference is 1 day and 1 hour, this will show 1 not 25. – Gary Myers Jul 8 '09 at 22:42
Another answer:
SQL> @id8
SQL> drop table holder ;
Table dropped.
SQL> create table holder (
2 beg_date timestamp,
3 end_date timestamp)
4 /
Table created.
SQL> INSERT INTO HOLDER VALUES(to_timestamp('2009-07-16:19:00:01.50','YYYY-MM-DD:HH24:MI:SS.FF'),
2 to_timestamp('2009-08-17:20:00','YYYY-MM-DD:HH24:MI'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL>
EXTRACT (HOUR FROM (END_DATE-BEG_DATE))*60*60+
EXTRACT (MINUTE FROM (END_DATE-BEG_DATE))*60+
EXTRACT (SECOND FROM (END_DATE-BEG_DATE)) DELTA
FROM holder
DELTA
----------
2768398.5