6 Replies Latest reply on Sep 12, 2019 9:36 AM by cormaco

# Sum of timestamp differences using group by function

Hi everyone,

from a table recording dates to make reservations for instruments in my lab, I need to generate a simple (dynamic) report with 3 columns in which the total amount of hours reserved for each user for each instrumentation is calculated.

Each reservation record stores a DATE_START and a DATE_END in 'timestamp' format, thus the time for each record is a difference between 2 timestamps.

Here's an example:

 ID APPLICANT PRODUCT_ID DATE_START DATE_END 1 luca 30 04-SEP-19 12.00.00.000000 PM 04-SEP-19 02.00.00.000000 PM 2 mario 31 04-SEP-19 01.00.00.000000 PM 04-SEP-19 02.00.00.000000 PM 3 mario 32 04-SEP-19 02.15.00.000000 PM 04-SEP-19 04.00.00.000000 PM 4 luca 31 05-SEP-19 01.00.00.000000 PM 05-SEP-19 02.15.00.000000 PM 5 luca 31 05-SEP-19 04.00.00.000000 PM 05-SEP-19 04.15.00.000000 PM 6 mario 30 05-SEP-19 01.00.00.000000 PM 05-SEP-19 02.15.00.000000 PM 7 paolo 31 06-SEP-19 10.00.00.000000 AM 06-SEP-19 02.00.00.000000 PM 8 paolo 31 06-SEP-19 03.00.00.000000 PM 06-SEP-19 04.00.00.000000 PM 9 paolo 30 06-SEP-19 05.00.00.000000 AM 06-SEP-19 06.10.00.000000 PM 10 luca 32 06-SEP-19 10.00.00.000000 AM 06-SEP-19 02.00.00.000000 PM 11 luca 32 06-SEP-19 02.00.00.000000 AM 06-SEP-19 04.30.00.000000 PM 12 paolo 31 07-SEP-19 01.00.00.000000 PM 07-SEP-19 02.00.00.000000 PM 13 mario 32 07-SEP-19 02.30.00.000000 PM 07-SEP-19 04.00.00.000000 PM 14 mario 31 07-SEP-19 05.10.00.000000 PM 07-SEP-19 07.00.00.000000 PM 15 paolo 30 07-SEP-19 09.00.00.000000 AM 07-SEP-19 02.10.00.000000 PM

The results should be like (I typed random numbers in HOURS column just for example) :

APPLICANTPRODUCT_IDHOURS
Luca306,5
Luca314
Luca323
Mario302,5
Mario315
Mario321,5
Paolo307
Paolo313

I used GROUP BY function to correlate users and products and calculated the difference between timestamps in hours but just for 1 record, e.g.:

select extract( hour from diff) hours

from (select DATE_END – DATE_START diff FROM RESERVATIONS where ID=411)

Thank you

K

• ###### 1. Re: Sum of timestamp differences using group by function

Kthun wrote:

Here's an example

Unfortunately it is not a usable example. Please repost the sample data in the form of DDL and DML scripts to create and populate the required table, or a WITH clause defining it as a CTE.

• ###### 2. Re: Sum of timestamp differences using group by function

Here are the results for luca:

```with reservations(ID, APPLICANT, PRODUCT_ID, DATE_START, DATE_END) as (
select 1,    'luca',    30,    to_timestamp('04-SEP-19 12.00.00.000000 PM','DD-MON-RR HH.MI:SS.FFX AM'), to_timestamp('04-SEP-19 02.00.00.000000 PM','DD-MON-RR HH.MI:SS.FFX AM') from dual union all
select 4,    'luca',    31,    to_timestamp('05-SEP-19 01.00.00.000000 PM','DD-MON-RR HH.MI:SS.FFX AM'), to_timestamp('05-SEP-19 02.15.00.000000 PM','DD-MON-RR HH.MI:SS.FFX AM') from dual union all
select 5,    'luca',    31,    to_timestamp('05-SEP-19 04.00.00.000000 PM','DD-MON-RR HH.MI:SS.FFX AM'), to_timestamp('05-SEP-19 04.15.00.000000 PM','DD-MON-RR HH.MI:SS.FFX AM') from dual union all
select 10,   'luca',    32,    to_timestamp('06-SEP-19 10.00.00.000000 AM','DD-MON-RR HH.MI:SS.FFX AM'), to_timestamp('06-SEP-19 02.00.00.000000 PM','DD-MON-RR HH.MI:SS.FFX AM') from dual union all
select 11,   'luca',    32,    to_timestamp('06-SEP-19 02.00.00.000000 AM','DD-MON-RR HH.MI:SS.FFX AM'), to_timestamp('06-SEP-19 04.30.00.000000 PM','DD-MON-RR HH.MI:SS.FFX AM') from dual
)
select APPLICANT, PRODUCT_ID, sum(extract(hour from (DATE_END - DATE_START)) + extract(minute from (DATE_END - DATE_START)) / 60 ) as sum
from reservations
group by APPLICANT, PRODUCT_ID
order by APPLICANT, PRODUCT_ID

APPL PRODUCT_ID        SUM
---- ---------- ----------
luca         30          2
luca         31        1.5
luca         32       18.5

```

It is still not possible to aggregate intervals, you can vote here:

• ###### 3. Re: Sum of timestamp differences using group by function

Sorry fac586,

I will format my next questions in the proper way.

Thank you

K

• ###### 4. Re: Sum of timestamp differences using group by function

Thank You Cormaco,

sorry for the wrong example format and thank again for your solution, that's what I needed.

K

• ###### 5. Re: Sum of timestamp differences using group by function

will you please inform us why you used RR in date format, asking because it has been recommended using YYYY instead of it.

regards

• ###### 6. Re: Sum of timestamp differences using group by function

I did this because the OP used 2digit years in the posted example data.

I agree with you that 4digit years should always be used instead.