Menu
I want to subtract 2 dates and represent the result in hour and minute in one decimal figure.
I have the following table and I am doing it in this way but the result is not as desired.
There is some slight variation, I'm sure this is simple arithmetic but I'm not getting it right.
select start_time, end_time, (end_time-start_time)*24 from
come_leav;
START_TIME END_TIME (END_TIME-START_TIME)*24 ------------------- ------------------- ------------------------ 21-06-2011 14:00:00 21-06-2011 16:55:00 2.9166667 21-06-2011 07:00:00 21-06-2011 16:50:00 9.8333333 21-06-2011 07:20:00 21-06-2011 16:30:00 9.1666667
I want the result (end_time-start_time) as below.
16:55- 14:00 = 2.55 16:50-07:00 = 9.5 16:30-7:20 = 9.1 and so on.
How can I do that?
366
To calculate the difference between the timestamps in Oracle, simply subtract the start timestamp from the end timestamp (here: arrival - departure ). The resulting column will be in INTERVAL DAY TO SECOND . The first number you see is the number of whole days that passed from departure to arrival .
With Oracle Dates, this is pretty trivial, you can get either TOTAL (days, hours, minutes, seconds) between 2 dates simply by subtracting them or with a little mod'ing you can get Days/Hours/Minutes/Seconds between. PL/SQL procedure successfully completed.
In Oracle, when you add or subtract a number to a date, the unit is days. So SYSDATE + 1 adds 1 day. To add some other increment, you just multiply or divide appropriately, i.e. SYSDATE - 1/24 subtracts an hour, SYSDATE - 1/24/60 subtracts a minute, etc.
Answer: Oracle supports date arithmetic and you can make expressions like "date1 - date2" using date subtraction to get the difference between the two dates. Once you have the date difference, you can use simple techniques to express the difference in days, hours, minutes or seconds.
SQL> edit
Wrote file afiedt.buf
1 select start_date
2 , end_date
3 , (24 * extract(day from (end_date - start_date) day(9) to second))
4 + extract(hour from (end_date - start_date) day(9) to second)
5 + ((1/100) * extract(minute from (end_date - start_date) day(9) to second)) as "HOUR.MINUTE"
6* from t
SQL> /
START_DATE END_DATE HOUR.MINUTE
------------------- ------------------- -----------
21-06-2011 14:00:00 21-06-2011 16:55:00 2.55
21-06-2011 07:00:00 21-06-2011 16:50:00 9.5
21-06-2011 07:20:00 21-06-2011 16:30:00 9.1
It should be noted for those coming across this code that the decimal portions are ACTUAL minute differences, and not part of an hour. .5, therefore, represents 50 minutes, not 30 minutes.
144
Try this
round(to_number(end_time - start_time) * 24)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
