Oracle - sum hours

Question

I have a table with some FROM / TO columns that represent hours:

FROM    TO
00:00   02:00
04:00   05:00
15:00   18:00

What I need to to sum the FROM / TO rows and see if they equal 24 hours. Not sure how to do that.....

Answer 1

Theory

To solve this you need to do 2 things:

1. Calculate the time difference between the two timestamps:

Calculating difference between two timestamps in Oracle in milliseconds

Another option is to just subtract the two columns and multiply by 24 (you should get back the hours).

2. Sum the result which is basically sum()

3. Check hours are greater than 24

Example 1

So a good first try would be something like:

SELECT sum((toTime-fromTime)*24) from data;

DbFiddle: https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fd91fd44b4c8ef7066dcdcc47ba64a9c

Problem with simplified Example

However, we have a problem because we don't take the day into account. So the next step is to also use the group by function and calculate the total hours by date

Here is a solution for that:

SELECT to_char(fromTime,'DD/MM/YYYY'), sum((toTime-fromTime)*24) from data
group by to_char(fromTime,'DD/MM/YYYY');

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=d861ee1b0252cb2dcd3bbf6901f12a5f

Add a true/false for If we have 24 hours:

SELECT to_char(fromTime,'DD/MM/YYYY'), 
case when sum((toTime-fromTime)*24) = 24 then 'YES' else 'NO' END as is24
from data
group by to_char(fromTime,'DD/MM/YYYY');

https://dbfiddle.uk/?rdbms=oracle_18&fiddle=ef0df7ad12c2da1a107b4628619df5a1

Answer 2

Although Menelaos has a good answer for working with real timestamps (and you could convert your strings to timestamps and use that answer), given that you have varchar2s and the hours are always even, you might also find this useful:

To get the part of the number before the colon, you can use SUBSTR and INSTR to do something like SUBSTR("TO", 0, INSTR("TO", ':')-1).

Then you can convert that to a number using TO_NUMBER by doing something like TO_NUMBER(SUBSTR("TO", 0, INSTR("TO", ':')-1)).

You can then subtract "FROM" from "TO" to get the difference for each row and use the aggregate SUM to total them. So the final query looks something like:

SELECT SUM(TO_NUMBER(SUBSTR("TO", 0, INSTR("TO", ':')-1)) - TO_NUMBER(SUBSTR("FROM", 0, INSTR("FROM", ':')-1)))
FROM mytable

A couple of comments:

If you know for sure that the times are always even hours, it would make more sense to just store them as numeric values. You could always present them to users in a different format, but storing the ":00" is just redundant and makes working with the values harder.

On the other hand, if you think that in the future there might be minutes or seconds or ranges that cross date boundaries, you're going to save yourself a lot of trouble just using a more appropriate type to start with (date or timestamp).

I double quoted the column names "TO"/"FROM" in my examples since they are reserved words. I'm not sure if using reserved words in column names is considered good practice in general, but it's something I tend to avoid.