how to convert mysql TIMESTAMPDIFF to oraclesql query

Question

TIMESTAMPDIFF(HOUR, pk.consprtydate, rs.consprtydate)

wanted the difference of hours from two dates and extract the hours in oracle sql

Does this answer your question? [How to write mysql TIMESTAMPDIFF function in Oracle sql query](https://stackoverflow.com/questions/28150837/how-to-write-mysql-timestampdiff-function-in-oracle-sql-query) — fab, Feb 19 '20 at 19:38

score 0 · Answer 1 · answered Feb 19 '20 at 18:27

0

In oracle, you can easily achieve it by substracting two date fields as following:

Date2 - Date1  -- returns number of days between dates
(Date2 - Date1)/24 -- returns number of days between dates
MONTHS_BETWEEN(Date1, Date2) -- returns number of months between dates

Cheers!!

answered Feb 19 '20 at 18:27

Popeye

35,427
4
10
31

2

?? `exp` and `exp/24` cannot give the same "number of days between dates"... – Akina Feb 19 '20 at 18:38

score 0 · Answer 2 · answered Feb 19 '20 at 19:04

It depends on the datatype. IF they are of Oracle type TIMESTAMP, then follow the methods already answered here

If they are of type date, then - as said by Tejash- simple subtraction gives the difference in days that you would then need to round to hours. (integer portion will be days = 24 hours, and the decimal portion will be in fractions of a day)

e.g.) to round down to the integer number of hours without a fractional part:

(days * 24 hours, plus the fraction of a day * 86400 seconds in a day / 3600 seconds in an hour)

SELECT   trunc(date1 - date2) * 24    +  trunc((mod (date1-date2,1) * 86400) / 3600) 
FROM DUAL

how to convert mysql TIMESTAMPDIFF to oraclesql query

2 Answers2