CODEWITHSUNDEEP
datetimeoracle11g
Shanaka
Shanaka

Reputation: 1738

get the date difference in milliseconds in oracle

I need to get he difference between end date and start date in milliseconds inside a view in oracle 11g. I can get these two dates from the database in 07-JUN-12 04.32.21.092000000 AM format. All I need is to find the diff of these kind of dates in milliseconds

Upvotes: 2

Views: 12086

Answers (2)

Shanaka
Shanaka

Reputation: 1738

SELECT ((extract(DAY FROM time2-time1)*24*60*60)+ 
(extract(HOUR FROM time2-time1)*60*60)+
(extract(MINUTE FROM time2-time1)*60)+
extract(SECOND FROM time2-time1)) *1000
as millisecs FROM dual;

can be done using above approach

Upvotes: 2

user158017
user158017

Reputation: 2991

select (DATE1 - DATE2) as days,
(DATE1 - DATE2) * 24 as hours,
(DATE1 - DATE2) * 24 * 60 as minutes,
(DATE1 - DATE2) * 24 * 60 * 60 as seconds,
(DATE1 - DATE2) * 24 * 60 * 60 * 1000 as milliseconds
 from dual

EDIT - I assumed DateTime type. However, Justin Cave's question is very relevant. A Timestamp is not the same as a DateTime, so my answer won't work if you are dealing with Timestamps.

In that case, see this http://www.dba-oracle.com/t_timestamp_math_elapsed_times.htm.

Upvotes: 2

Related Questions