CODEWITHSUNDEEP
mysqltime
a coder
a coder

Reputation: 7659

Getting hours worked between sets of dates

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.

Upvotes: 0

Views: 78

Answers (2)

RolandoMySQLDBA
RolandoMySQLDBA

Reputation: 44363

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 !!!

Upvotes: 2

Jean-Bernard Pellerin
Jean-Bernard Pellerin

Reputation: 12670

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

Upvotes: 2

Related Questions