CODEWITHSUNDEEP
sqloracle-database
Ethan Bradberry
Ethan Bradberry

Reputation: 107

ORACLE SQL - TIME DIFFERENCE

i'm trying to get the minutes difference between some hours/minutes columns in Oracle SQL

TO_DATE((resolved_hour || ':' || resolved_minute),'HH24:M1')-TO_DATE((reported_hour || ':' || repoted_minute),'HH24:MI') as REPORTED

this is the code have come up with

the raw data is 

TO_DATE((resolved_hour || ':' || resolved_minute),'HH24:M1')AS RESOLVED

= 14:45

TO_DATE((reported_hour || ':' || repoted_minute),'HH24:MI') as REPORTED

= 14:30

so i would expect 15 as the output.

however i'm getting 

0.0104166666666666666666666666666666666667

can anyone advise of how i can achieve this?

there are seperate hour/minute columns for both.

any help would be great

thanks

Upvotes: 0

Views: 1457

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

Why not just use arithmetic?

select (resolved_hour - reported_hour) * 60 + (resolved_minute - reported_minute)

Upvotes: 2

ESG
ESG

Reputation: 9425

In Oracle, the difference between two dates is expressed in days. Or, in this case, as the fraction of a day.

If you multiply 0.0104166666666666666666666666666666666667 by 24 * 60, you get... something close to 15.

Upvotes: 4

Related Questions