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

    Sum of timestamp differences using group by function

    Kthun

      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:

       

      IDAPPLICANTPRODUCT_IDDATE_STARTDATE_END
      1luca3004-SEP-19 12.00.00.000000 PM04-SEP-19 02.00.00.000000 PM
      2mario3104-SEP-19 01.00.00.000000 PM04-SEP-19 02.00.00.000000 PM
      3mario3204-SEP-19 02.15.00.000000 PM04-SEP-19 04.00.00.000000 PM
      4luca3105-SEP-19 01.00.00.000000 PM05-SEP-19 02.15.00.000000 PM
      5luca3105-SEP-19 04.00.00.000000 PM05-SEP-19 04.15.00.000000 PM
      6mario3005-SEP-19 01.00.00.000000 PM05-SEP-19 02.15.00.000000 PM
      7paolo3106-SEP-19 10.00.00.000000 AM06-SEP-19 02.00.00.000000 PM
      8paolo3106-SEP-19 03.00.00.000000 PM06-SEP-19 04.00.00.000000 PM
      9paolo3006-SEP-19 05.00.00.000000 AM06-SEP-19 06.10.00.000000 PM
      10luca3206-SEP-19 10.00.00.000000 AM06-SEP-19 02.00.00.000000 PM
      11luca3206-SEP-19 02.00.00.000000 AM06-SEP-19 04.30.00.000000 PM
      12paolo3107-SEP-19 01.00.00.000000 PM07-SEP-19 02.00.00.000000 PM
      13mario3207-SEP-19 02.30.00.000000 PM07-SEP-19 04.00.00.000000 PM
      14mario3107-SEP-19 05.10.00.000000 PM07-SEP-19 07.00.00.000000 PM
      15paolo3007-SEP-19 09.00.00.000000 AM07-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)

       

      Any suggestion please?

       

      Thank you

      K

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

          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
            cormaco

            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:

            SUM on INTERVAL

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

              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
                Kthun

                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
                  Ora_Learner890

                  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
                    cormaco

                    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.