OraFAQ Forum: SQL & PL/SQL » Calc elapsed time per event & date

Home » SQL & PL/SQL » SQL & PL/SQL » Calc elapsed time per event & date (11.2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Calc elapsed time per event & date [message #636723]

Thu, 30 April 2015 16:32

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

I'd like to calculate the elapsed time in Hours:Minutes between each row and when each date(start date) is the same.
I use a partition on "Start Date" to determine the "integer" number of minutes for that calc.

here's my sample data and results. I think the calculations are correct but is there a better way then what I attempted??

WITH data AS
 (SELECT 1 id,
         to_date('4/25/2015 8:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/26/2015 9:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/26/2015 8:57:47 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/26/2015 10:01:49 AM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/25/2015 11:02:06 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/25/2015 12:22:37 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/26/2015 11:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         NULL end_date
    FROM dual)
SELECT id,
       start_date,
       end_date,
       
       --Elapsed Time - SY version
       floor((end_date - start_date) * 24) ||
       to_char(DATE '1-1-1' + (end_date - start_date), ':mi') elapsetime_sy,
       
       --Elapsed Time
       CASE
         WHEN end_date IS NULL THEN
          NULL
         ELSE
          to_char(floor(to_number(end_date - start_date) * 1440 / 60)) || ':' ||
          to_char(MOD(floor(to_number(end_date - start_date) * 1440), 60),
                  'FM00')
       END elapsetime,
       
       --Elapsed Time minutes (Sum of Start Date)
       SUM(floor(to_number(end_date - start_date) * 1440)) over(PARTITION BY trunc(start_date)) sum_elapsetime_integer,
       
       --Elapsed Time minutes(by date)
       CASE
         WHEN SUM(floor(to_number(end_date - start_date) * 1440))
          over(PARTITION BY trunc(start_date)) IS NULL THEN
          NULL
         ELSE
          to_char(floor(SUM(floor(to_number(end_date - start_date) * 1440))
                        over(PARTITION BY trunc(start_date)) / 60)) || ':' ||
          to_char(MOD(SUM(floor(to_number(end_date - start_date) * 1440))
                      over(PARTITION BY trunc(start_date)),
                      60),
                  'FM00')
       END elapsedtime_date

/*
       --Elapsed Time minutes(by date)- SY version
       SUM(floor((end_date - start_date) * 24) ||
           to_char(DATE '1-1-1' + (end_date - start_date), ':mi:ss')) over(PARTITION BY trunc(start_date)) elapsetime_date_sy
*/

  FROM data
 ORDER BY id, start_date;

Results

ID	START_DATE	        END_DATE	        ELAPSETIME_SY	ELAPSETIME	SUM_ELAPSETIME_INTEGER	ELAPSEDTIME_DATE
1	4/25/2015 8:00:00 AM	4/26/2015 9:00:00 AM	25:00	        25:00	        1580	                26:20
1	4/25/2015 11:02:06 AM	4/25/2015 12:22:37 PM	1:20	        1:20	        1580	                26:20
1	4/26/2015 8:57:47 AM	4/26/2015 10:01:49 AM	1:04	        1:04	        64	                1:04
1	4/26/2015 11:00:00 AM				                                64	                1:04

I don't like the fact that I need a case statement to check for a value because of the ":" format between the HH:MI.

I'd like to use a variation of "SY" logic when trying to calc "elapsedtime_date" but I couldn't figure out the correct syntax.

Thanks in advance. I'll try to answer question as soon as I can.

Report message to a moderator

Re: Calc elapsed time per event & date [message #636731 is a reply to message #636723]

Thu, 30 April 2015 21:47

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

In case it's hard to tell the last column
"ELAPSEDTIME_DATE"
26:20

represents the values of the first two rows(Start Date of 4/25/2015)
"ELAPSEDTIME'
25:00
1:20

OR

26:20

While
"ELAPSEDTIME'
1:04

OR

1:04

The values of the last two rows(Start Date of 4/26/2015)

Report message to a moderator

Re: Calc elapsed time per event & date [message #636744 is a reply to message #636723]

Fri, 01 May 2015 06:08

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

lott42 wrote on Thu, 30 April 2015 17:32

I don't like the fact that I need a case statement to check for a value because of the ":" format between the HH:MI.

WITH data AS
 (SELECT 1 id,
         to_date('4/25/2015 8:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/26/2015 9:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/26/2015 8:57:47 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/26/2015 10:01:49 AM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/25/2015 11:02:06 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/25/2015 12:22:37 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/26/2015 11:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         NULL end_date
    FROM dual)
SELECT  start_date,
        end_date,
        CASE
          WHEN end_date IS NULL THEN NULL
          ELSE to_char(floor(to_number(end_date - start_date) * 1440 / 60)) || ':' ||
               to_char(MOD(floor(to_number(end_date - start_date) * 1440), 60),'FM00')
        END elapsetime1,
        to_char(floor(to_number(end_date - start_date) * 1440 / 60)) ||
          to_char(MOD(floor(to_number(end_date - start_date) * 1440), 60) / 100,'FMD00','nls_numeric_characters=:,') elapsetime2
  FROM  data
/

START_DATE          END_DATE            ELAPSETIME1 ELAPSETIME2
------------------- ------------------- ----------- -----------
04/25/2015 08:00:00 04/26/2015 09:00:00 25:00       25:00
04/25/2015 11:02:06 04/25/2015 12:22:37 1:20        1:20
04/26/2015 08:57:47 04/26/2015 10:01:49 1:04        1:04
04/26/2015 11:00:00

SQL>

SY.

Report message to a moderator

Re: Calc elapsed time per event & date [message #636748 is a reply to message #636744]

Fri, 01 May 2015 07:23

cookiemonster
Messages: 13975
Registered: September 2008
Location: Rainy Manchester

Senior Member

All those to_number calls can be removed. date - date returns a number.

Report message to a moderator

Re: Calc elapsed time per event & date [message #636750 is a reply to message #636748]

Fri, 01 May 2015 07:56

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

cookiemonster wrote on Fri, 01 May 2015 08:23

All those to_number calls can be removed. date - date returns a number.

Good catch. And another solution:

WITH data AS
 (SELECT 1 id,
         to_date('4/25/2015 8:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/26/2015 9:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/26/2015 8:57:47 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/26/2015 10:01:49 AM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/25/2015 11:02:06 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/25/2015 12:22:37 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/26/2015 11:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         NULL end_date
    FROM dual)
SELECT  start_date,
        end_date,
        CASE
          WHEN end_date IS NULL THEN NULL
          ELSE to_char(floor((end_date - start_date) * 1440 / 60)) || ':' ||
               to_char(MOD(floor((end_date - start_date) * 1440), 60),'FM00')
        END elapsetime1,
        to_char(floor((end_date - start_date) * 1440 / 60)) ||
          to_char(MOD(floor((end_date - start_date) * 1440), 60) / 100,'FMD00','nls_numeric_characters=:,') elapsetime2,
        to_char(floor((end_date - start_date) * 1440 / 60)) ||
          to_char(date '1-1-1' + (end_date - start_date),':mi') elapsetime3
  FROM  data
/

START_DATE          END_DATE            ELAPSETIME1 ELAPSETIME2 ELAPSETIME3
------------------- ------------------- ----------- ----------- -----------
04/25/2015 08:00:00 04/26/2015 09:00:00 25:00       25:00       25:00
04/25/2015 11:02:06 04/25/2015 12:22:37 1:20        1:20        1:20
04/26/2015 08:57:47 04/26/2015 10:01:49 1:04        1:04        1:04
04/26/2015 11:00:00

SQL>

SY.

Report message to a moderator

Re: Calc elapsed time per event & date [message #636753 is a reply to message #636750]

Fri, 01 May 2015 10:14

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

Thanks guys!! Now to extrapolate one step further on the last column "ELAPSEDTIME_DATE"(sum of time for the same start_date) from original

WITH data AS
 (SELECT 1 id,
         to_date('4/25/2015 8:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/26/2015 9:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/26/2015 8:57:47 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/26/2015 10:01:49 AM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/25/2015 11:02:06 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/25/2015 12:22:37 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/26/2015 11:00:00 AM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         NULL end_date
    FROM dual)
SELECT start_date,
       end_date,
       
       to_char(floor((end_date - start_date) * 1440 / 60)) ||
       to_char(MOD(floor((end_date - start_date) * 1440), 60) / 100,
               'FMD00',
               'nls_numeric_characters=:,') elapsetime2,
       
       to_char(SUM(floor(to_number(end_date - start_date) * 1440 / 60))
               over(PARTITION BY trunc(start_date))) ||
       to_char(MOD(SUM(floor(to_number(end_date - start_date) * 1440))
                   over(PARTITION BY trunc(start_date)),
                   60) / 100,
               'FMD00',
               'nls_numeric_characters=:,') elapsetime2a,
       
       to_char(floor((end_date - start_date) * 1440 / 60)) ||
       to_char(DATE '1-1-1' + (end_date - start_date), ':mi') elapsetime3,
       
       SUM(floor(to_number(end_date - start_date) * 1440 / 60)) over(PARTITION BY trunc(start_date)) || to_char(DATE '1-1-1' + (SUM(floor(to_number(end_date - start_date))) over(PARTITION BY trunc(start_date))), ':mi') elapsetime3a,
       SUM(floor(to_number(end_date - start_date) * 1440 / 60)) over(PARTITION BY trunc(start_date)) || to_char(DATE '1-1-1' + (MOD(SUM(floor(to_number(end_date - start_date) * 1440)) over(PARTITION BY trunc(start_date)), 60) / 100), ':mi') elapsetime3b
  FROM data

I added columns "elapsetime2a", "elapsetime3a", "elapsetime3b" BUT I can't seem to get "elapsetime3a" OR "elapsetime3b" correct. I'm not computing the minutes correct for some reason. Any more thoughts??

Which yields these results

START_DATE	        END_DATE	        ELAPSETIME2	ELAPSETIME2A	ELAPSETIME3	ELAPSETIME3A	ELAPSETIME3B
4/25/2015 8:00:00 AM	4/26/2015 9:00:00 AM	25:00	        26:20	        25:00	        26:00	        26:48
4/25/2015 11:02:06 AM	4/25/2015 12:22:37 PM	1:20	        26:20	        1:20	        26:00	        26:48
4/26/2015 8:57:47 AM	4/26/2015 10:01:49 AM	1:04	        1:04	        1:04	        1:00	        1:57
4/26/2015 11:00:00 AM			                        1:04		                1:00	        1:57

I think the values from column "elapsetime2a" are correct.

Thanks

Report message to a moderator

Re: Calc elapsed time per event & date [message #636759 is a reply to message #636753]

Fri, 01 May 2015 12:52

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

OK, Now I'm more confused. I've modified the data from earlier posting to try a different set of data. I added another column "elapsetime4". It shows a "different" total.

It looks like "elapsetime4" is the correct one so maybe my calc for "ELAPSETIME2A", "ELAPSETIME3A", and "ELAPSETIME3B" are incorrect.

WITH data AS
 (SELECT 1 id,
         to_date('4/25/2015 2:55:00 PM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/25/2015 5:34:00 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/25/2015 3:41:31 PM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/25/2015 6:22:24 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/25/2015 4:09:24 PM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         NULL end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/25/2015 5:44:00 PM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/25/2015 10:00:00 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual
  UNION
  SELECT 1 id,
         to_date('4/25/2015 6:21:10 PM', 'mm/dd/yyyy hh:mi:ss am') start_date,
         to_date('4/25/2015 6:21:28 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
    FROM dual)
SELECT start_date,
       end_date,
       
       to_char(floor((end_date - start_date) * 1440 / 60)) ||
       to_char(MOD(floor((end_date - start_date) * 1440), 60) / 100,
               'FMD00',
               'nls_numeric_characters=:,') elapsetime2,
       
       to_char(SUM(floor(to_number(end_date - start_date) * 1440 / 60))
               over(PARTITION BY trunc(start_date))) ||
       to_char(MOD(SUM(floor(to_number(end_date - start_date) * 1440))
                   over(PARTITION BY trunc(start_date)),
                   60) / 100,
               'FMD00',
               'nls_numeric_characters=:,') elapsetime2a,
       
       SUM(floor(to_number(end_date - start_date) * 1440 / 60)) over(PARTITION BY trunc(start_date)) || to_char(DATE '1-1-1' + (SUM(floor(to_number(end_date - start_date))) over(PARTITION BY trunc(start_date))), ':mi') elapsetime3a,
       SUM(floor(to_number(end_date - start_date) * 1440 / 60)) over(PARTITION BY trunc(start_date)) || to_char(DATE '1-1-1' + (MOD(SUM(floor(to_number(end_date - start_date) * 1440)) over(PARTITION BY trunc(start_date)), 60) / 100), ':mi') elapsetime3b,

       to_char(floor((end_date - start_date) * 1440 / 60)) ||
       to_char(DATE '1-1-1' + (end_date - start_date), ':mi') elapsetime3,
       
       to_char(floor(SUM(floor(to_number(end_date - start_date) * 1440))
                     over(PARTITION BY trunc(start_date)) / 60)) || ':' ||
       to_char(MOD(SUM(floor(to_number(end_date - start_date) * 1440))
                   over(PARTITION BY trunc(start_date)),
                   60),
               'FM00') elapsetime4

  FROM data

Which yields these results

START_DATE	        END_DATE	        ELAPSETIME2	ELAPSETIME2A	ELAPSETIME3A	ELAPSETIME3B	ELAPSETIME3	ELAPSETIME4
4/25/2015 2:55:00 PM	4/25/2015 5:34:00 PM	2:39	        8:35	        8:00	        8:24	        2:39	        9:35
4/25/2015 3:41:31 PM	4/25/2015 6:22:24 PM	2:40	        8:35	        8:00	        8:24	        2:40	        9:35
4/25/2015 6:21:10 PM	4/25/2015 6:21:28 PM	0:00	        8:35	        8:00	        8:24	        0:00	        9:35
4/25/2015 5:44:00 PM	4/25/2015 10:00:00 PM	4:16	        8:35	        8:00	        8:24	        4:16	        9:35
4/25/2015 4:09:24 PM			                        8:35	        8:00	        8:24		                9:35

The total for Hour:Minute of (2:39, 2:40, 4:16) = 9:35 so somewhere my calc. are wrong

Thanks, Again

Report message to a moderator

Re: Calc elapsed time per event & date [message #636762 is a reply to message #636759]

Fri, 01 May 2015 14:29

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

SQL> WITH data AS
  2   (SELECT 1 id,
  3           to_date('4/25/2015 2:55:00 PM', 'mm/dd/yyyy hh:mi:ss am') start_date,
  4           to_date('4/25/2015 5:34:00 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
  5      FROM dual
  6    UNION
  7    SELECT 1 id,
  8           to_date('4/25/2015 3:41:31 PM', 'mm/dd/yyyy hh:mi:ss am') start_date,
  9           to_date('4/25/2015 6:22:24 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
 10      FROM dual
 11    UNION
 12    SELECT 1 id,
 13           to_date('4/25/2015 4:09:24 PM', 'mm/dd/yyyy hh:mi:ss am') start_date,
 14           NULL end_date
 15      FROM dual
 16    UNION
 17    SELECT 1 id,
 18           to_date('4/25/2015 5:44:00 PM', 'mm/dd/yyyy hh:mi:ss am') start_date,
 19           to_date('4/25/2015 10:00:00 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
 20      FROM dual
 21    UNION
 22    SELECT 1 id,
 23           to_date('4/25/2015 6:21:10 PM', 'mm/dd/yyyy hh:mi:ss am') start_date,
 24           to_date('4/25/2015 6:21:28 PM', 'mm/dd/yyyy hh:mi:ss am') end_date
 25      FROM dual)
 26  SELECT  start_date,
 27          end_date,
 28          to_char(floor(SUM(end_date - start_date) OVER(PARTITION BY trunc(start_date)) * 1440 / 
60)) ||
 29            to_char(MOD(floor(SUM(end_date - start_date) OVER(PARTITION BY trunc(start_date)) * 1
440), 60) / 100,'FMD00','nls_numeric_characters=:,') elapsetime2,
 30          to_char(floor(SUM(end_date - start_date) OVER(PARTITION BY trunc(start_date)) * 1440 / 
60)) ||
 31            to_char(date '1-1-1' + SUM(end_date - start_date) OVER(PARTITION BY trunc(start_date)
),':mi') elapsetime3
 32    FROM  data
 33  /

START_DAT END_DATE  ELAPSETIME2 ELAPSETIME3
--------- --------- ----------- -----------
25-APR-15 25-APR-15 9:36        9:36
25-APR-15 25-APR-15 9:36        9:36
25-APR-15 25-APR-15 9:36        9:36
25-APR-15 25-APR-15 9:36        9:36
25-APR-15           9:36        9:36

SQL>

SY.

Report message to a moderator

Re: Calc elapsed time per event & date [message #636769 is a reply to message #636762]

Fri, 01 May 2015 16:11

lott42_gmail
Messages: 146
Registered: June 2010

Senior Member

Awesome, Thanks Solomon!!

Not to be "nit-picky" but shouldn't the result be "9:35". I realize it must be a rounding issue with the seconds but is there a way to "trunc" the seconds before the calculation??

to_char(floor(SUM(floor(to_number(end_date - start_date) * 1440))
                     over(PARTITION BY trunc(start_date)) / 60)) || ':' ||
       to_char(MOD(SUM(floor(to_number(end_date - start_date) * 1440))
                   over(PARTITION BY trunc(start_date)),
                   60),
               'FM00') elapsetime4

Again, I'm just "nit picking" now so it's not too important but just curious is all...

Report message to a moderator

Re: Calc elapsed time per event & date [message #636771 is a reply to message #636769]

Fri, 01 May 2015 17:17

Solomon Yakobson
Messages: 3312
Registered: January 2010
Location: Connecticut, USA

Senior Member

Yes, it is rounding issue. FLOOR(SUM(non-negative-number)) >= SUM(FLOOR(non-negative-number)). You need to add first and then floor.

SY.

Report message to a moderator

Previous Topic:	Need help to Learn PL\SQL
Next Topic:	Partition & Sub partition Table Creation Error

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Jul 28 00:13:04 CDT 2026