CODEWITHSUNDEEP
mysqlsql
ehed
ehed

Reputation: 862

Calculating time difference between rows in mysql

I have a table containing a list of songs that were played with the following columns of interest:

id int
name varchar(255)
duration int(11)
created datetime
status varchar(255)

The status field is effectively unused, e.g. it's always new or something. What I'd like to do is to query the data and overwrite the 'status' field in the output to 'skipped' if created + datetime is different than the created time of the song that came next.

Possible? I'm sure I could process this in code and update the status in the table but I'm wondering if it might be achievable without doing that.

here is what I have so far:

mysql> select id, duration, created, date_add(created, interval duration second) as projected_end, status from activity order by id desc limit 3;
+---------+----------+---------------------+----------------------------+--------+
| id      | duration | created             | projected_end              | status |
+---------+----------+---------------------+----------------------------+--------+
| 4800884 | 1119     | 2017-09-15 11:06:18 | 2017-09-15 11:24:57.000000 | new    |
| 4800842 | 1004     | 2017-09-15 11:03:36 | 2017-09-15 11:20:20.000000 | new    |
| 4800807 | 1185     | 2017-09-15 10:43:47 | 2017-09-15 11:03:32.000000 | new    |
+---------+----------+---------------------+----------------------------+--------+
    3 rows in set (0.00 sec)

So in this case song id 4800842 was skipped as it should have ended at 11:20 but the next one started at 11:06.

What I'm hoping to be able to do is alter the status field in the output based on a comparison of the projected_end of the earlier track and the created value of the subsequent track.

Any help is appreciated, thank you!

Upvotes: 0

Views: 61

Answers (1)

ScottieB
ScottieB

Reputation: 4052

MySQL just released window functions!

So you could use LEAD to get the next row's created field, and simply compare it to the current row's projected_end.

Eg 

SELECT *
,LEAD(created) OVER (ORDER BY created) as next_created
,LEAD(created) OVER (ORDER BY created) < projected_end as was_skipped
FROM [your_table]

Upvotes: 1

Related Questions