Getting hours worked between sets of dates

Question

login/logout events are recorded in a table like this:

user_id     object_id   date_event
7           2           2013-05-03 08:37:11
7           3           2013-05-03 12:43:51
7           2           2013-05-03 15:33:41
7           3           2013-05-03 17:10:01

There are other objects logged, but 2 and 3 are the important ones (2=login, 3=logout).

I'm having trouble with a query that will show a grand total of hours logged in. I used this as a starting point, but my query returns null for the total time logged in.

        SELECT 
            el.user_id, 
            TIME(SUM(el.date_event*(1-2*el.object_id))) AS total_time
        FROM 
            event_log el 
        WHERE 
            DATE(el.date_event) = '2013-05-03' AND 
            el.object_id IN (2,3) AND 
            el.user_id = 9
        GROUP BY 
            el.user_id;

This yeilds:

user_id     total_time
9           NULL

Where I expect

user_id     total_time
9           05:43:00

Why is it returning NULL? There are no nulls in the data set.

In the other example where you are trying to input your object_id he was using 1s and 0s and you are using 2s and 3s. He was using the 1s and 0s to set positives and negatives. Yours will not work the same. Try a case statement to turn the 2 to a 1 and the three to a 0. — Leeish, May 03 '13 at 21:59
I have no idea if that will fix it, but I at least see that is where your logic differs. — Leeish, May 03 '13 at 22:01

Jean-Bernard Pellerin · Accepted Answer · 2013-05-03T22:19:16.217

2

Supposing 3 is logout and 2 is login

SELECT  T1.user_id, 
        T1.date_event,
        MIN(TimeDiff((T2.date_event), T1.date_event )) AS DaysDiff
FROM    YourTable T1
        LEFT JOIN YourTable T2
            ON T1.user_id = T2.user_id
            AND T2.date_event > T1.date_event
WHERE T1.object_id = 2 AND T2.object_id = 3
GROUP BY   T1.user_id, 
        T1.date_event

edited May 03 '13 at 22:19

answered May 03 '13 at 22:03

Jean-Bernard Pellerin

12,556
10
57
79

The only issue here is that it doesn't handle missing data well. For example two consecutive log ins without a log out. It's possible to deal with that but not easily with MySQL – Conrad Frix May 03 '13 at 22:10
1

You just need to reverse the arguments to TIMEDIFF and take the min after rather than before. [See this fiddle](http://sqlfiddle.com/#!2/bf2c0/12) to fix it – Conrad Frix May 03 '13 at 22:17
1

@ConradFrix Thank you for taking the time to help me see what I had done wrong and fixing it. I went ahead and gave you a couple of upvotes since you could really have submitted this as your own answer. – Jean-Bernard Pellerin May 03 '13 at 22:20

score 2 · Answer 2 · answered May 03 '13 at 22:33

I have another approach

SET @given_user = 7;
SET @dt = '2013-05-03';
SET @dt1 = @dt + INTERVAL 0 SECOND;
SET @dt2 = @dt + INTERVAL 1 DAY;
SELECT
    SEC_TO_TIME(SUM(dts)) TotalTime
FROM
(
    SELECT
        (IF(object_id=2,-1,1) *
        (UNIX_TIMESTAMP(date_event) -
        UNIX_TIMESTAMP(DATE(date_event) +INTERVAL 0 SECOND))) dts
    FROM
        event_log
    WHERE
        user_id=@given_user AND
        object_id IN (2,3) AND
        date_event >= @dt1 AND
        date_event < @dt2
) A;

Here is your sample data

use test
DROP TABLE IF EXISTS event_log;
CREATE TABLE event_log
(
    id INT NOT NULL AUTO_INCREMENT,
    user_id INT NOT NULL,
    object_id INT NOT NULL,
    date_event DATETIME,
    PRIMARY KEY (id)
);
INSERT into event_log
(user_id,object_id,date_event) VALUES
(7,2,'2013-05-03 08:37:11'),
(7,3,'2013-05-03 12:43:51'),
(7,2,'2013-05-03 15:33:41'),
(7,3,'2013-05-03 17:10:01');

Here it is loaded

use test
Database changed
mysql> DROP TABLE IF EXISTS event_log;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE event_log
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     user_id INT NOT NULL,
    ->     object_id INT NOT NULL,
    ->     date_event DATETIME,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.05 sec)

mysql> INSERT into event_log
    -> (user_id,object_id,date_event) VALUES
    -> (7,2,'2013-05-03 08:37:11'),
    -> (7,3,'2013-05-03 12:43:51'),
    -> (7,2,'2013-05-03 15:33:41'),
    -> (7,3,'2013-05-03 17:10:01');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql>

Here is the query executed

mysql> SET @given_user = 7;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @dt = '2013-05-03';
Query OK, 0 rows affected (0.00 sec)

mysql> SET @dt1 = @dt + INTERVAL 0 SECOND;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @dt2 = @dt + INTERVAL 1 DAY;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT
    ->     SEC_TO_TIME(SUM(dts)) TotalTime
    -> FROM
    -> (
    ->     SELECT
    ->         (IF(object_id=2,-1,1) *
    ->         (UNIX_TIMESTAMP(date_event) -
    ->         UNIX_TIMESTAMP(DATE(date_event) +INTERVAL 0 SECOND))) dts
    ->     FROM
    ->         event_log
    ->     WHERE
    ->         user_id=7 AND
    ->         object_id IN (2,3) AND
    ->         date_event >= @dt1 AND
    ->         date_event < @dt2
    -> ) A;
+-----------+
| TotalTime |
+-----------+
| 05:43:00  |
+-----------+
1 row in set (0.00 sec)

mysql>

Give it a Try !!!

Getting hours worked between sets of dates

2 Answers2