Date difference between rows of records

ngaisteve · #1 (**permalink**) 07-27-10, 00:25

ID | STATUS | DATE | SYSTEMTIME
--------------------------------------------------------------------
ABC | S | 2010-01-14 | 2010-01-15-00.03.07.205829
ABC | E | 2010-01-14 | 2010-01-15-00.16.16.473507
ABC | S | 2010-01-15 | 2010-01-15-22.52.54.072351
ABC | E | 2010-01-15 | 2010-01-15-23.04.34.257471

Hi, I would like to ask, is it possible for SQL in DB2 to calculate the above test data to something like below?

PROGID | DATE | Duration (hh:mm:ss)
-------------------------------------------------------
ABC | 2010-01-14 | 00:13:09
ABC | 2010-01-15 | 00:11:40

Note: S - Start; E - End.

Marcus_A · #2 (**permalink**) 07-27-10, 01:42

Yes, it is possible.

dav1mo · #3 (**permalink**) 07-27-10, 08:02

you would have to join the table to itself in a subquery getting the max SYSTEMTIME of the E status that is less than the SYSTEMTIME of your S status.
Dave

stolze · #4 (**permalink**) 07-27-10, 08:03

You can do a self-join and then calculate the difference:

Code:

SELECT t1.progid, t1.date, t2.systemtime - t1.systemtime
FROM   ... AS t1 JOIN ... AS t2 ON ( t1.progid = t2.progid AND t1.date = t2.date )
WHERE  t1.status = 'S' AND
       t2.status = 'E'

The basic idea is to get the start and end timestamps into a single row so that you can process that row in the SELECT list and/or WHERE clause.

tonkuma · #5 (**permalink**) 07-27-10, 08:55

Another way may be like this:

Code:

SELECT id AS progid , date
     , /* calculate a difference of
            MAX(CASE status WHEN 'E' THEN systemtime END)
            and
            MAX(CASE status WHEN 'S' THEN systemtime END)
          then convert it to data type TIME.
       */ AS duration
  FROM test_data
 GROUP BY
       id , date
;

ngaisteve · #6 (**permalink**) 07-28-10, 06:40

okay, thanks a lot. I will try them out.

ngaisteve · #7 (**permalink**) 07-28-10, 22:22

Quote:

Originally Posted by stolze

You can do a self-join and then calculate the difference:

Code:

SELECT t1.progid, t1.date, t2.systemtime - t1.systemtime
FROM   ... AS t1 JOIN ... AS t2 ON ( t1.progid = t2.progid AND t1.date = t2.date )
WHERE  t1.status = 'S' AND
       t2.status = 'E'

The basic idea is to get the start and end timestamps into a single row so that you can process that row in the SELECT list and/or WHERE clause.

It works. Thanks a lot.

ngaisteve · #8 (**permalink**) 07-28-10, 22:49

To be more accurate, change t2.systemtime - t1.systemtime to
timestampdiff(2, char(timestamp(to_char(timestamp(t2.systemtime), 'yyyy-mm-dd hh24:mi:ss')) - timestamp(to_char(timestamp(t1.systemtime), 'yyyy-mm-dd hh24:mi:ss')) ))

Internet Services

Web Development

Digital Marketing

Consumer Tech