MySQL Date Difference Query

Question

I am trying to write an extensive query that will accomplish the following tasks;

Select the fields "time_stamp" and "status" from the "time_stamps" DB and order them by "time_stamp" ASC.
Of the fields returned, for each pair of time stamps (1 in and 1 out), I need to get the difference in hours.

Here is what I have so far in the query.

SELECT time_stamp AS 'time', status AS 'status' FROM time_stamps WHERE user_id = '55' ORDER BY time

Then I have this query which gets the difference, in hours, from two dates.

SELECT TIMESTAMPDIFF(SECOND, '2016-04-26 07:01:26', '2016-04-26 15:07:55') / 3600.0

Ideally, I would like the query to return data that looks like this;

time_stamp          status  difference
2016-04-18 06:57:04     1   
2016-04-18 11:19:49     0   8.11
2016-04-18 11:41:01     1   
2016-04-18 15:21:02     0   3.67
2016-04-19 07:06:16     1   
2016-04-19 10:58:34     0   3.87
2016-04-19 11:18:50     1   
2016-04-19 15:16:00     0   3.95
2016-04-20 06:58:30     1   
2016-04-20 12:46:33     0   5.80

I was thinking that I may have to write a stored procedure or something similar to accomplish this but I'm not completely sure. Can someone point me in the right direction on where to go from here? I am not looking for someone to write this whole query for me, just a nudge in the right direction. Thanks!

score 0 · Answer 1 · answered Apr 27 '16 at 14:24

Just a nudge in the right direction, as you asked:

How about

SELECT @previous, @previous := `time_stamp`, `status`
FROM time_stamps 
ORDER BY `time_stamp`;

and work from that.

Note that the column-order is important in this case. If you would first assign before retrieval it won't (obviously maybe) work.

Proof of concept: http://sqlfiddle.com/#!9/4e17b5/2

score 0 · Accepted Answer · answered Apr 27 '16 at 14:25

SELECT  time_stamp, status,
        TIMESTAMPDIFF(
                SECOND,
                (
                SELECT  ti.time_stamp
                FROM    time_stamps ti
                WHERE   ti.user_id = t.user_id
                        AND ti.time_stamp <= t.time_stamp
                        AND t.status = 0
                        AND ti.status = 1
                ORDER BY
                        ti.user_id DESC, ti.time_stamp DESC
                LIMIT 1
                ),
                time_stamp
                )
FROM    time_stamps t
WHERE   user_id = :myuserid

Create a composite index on (user_id, time_stamp) for this to work fast.

With some tinkering I got your code to work perfectly. Thanks for all of your help Quassnoi! — Kronos1, Apr 27 '16 at 14:45

MySQL Date Difference Query

2 Answers2