This question already has an answer here:
I am trying to think of the most efficient way to count specific rows in a MySQL table.
My table contains a datetime column called date_time and I want to count the number of rows where there is at least 30 minutes between each date_time.
For example, lets say I have the table below:
id date_time
1 2013-08-23 00:30:00
2 2013-08-23 00:45:00
3 2013-08-23 01:01:00
4 2013-08-23 02:30:00
5 2013-08-23 02:45:00
If I only want to include an entry if there is at least 30 minutes since the last entry, then the count would include id #1 and id #4, thus the count would be 2.
I am trying to come up with a clean script to do this, is there any special kind of query that will help me accomplish this?
</div>
Very simple solution for that is use DATE_FORMAT MySQL function.
http://sqlfiddle.com/#!2/3b793/4
Query
SELECT
DATE_FORMAT(`datef`, '%i') AS `Fields`,
`datef`
FROM `dates`
WHERE DATE_FORMAT(`datef`, '%i') = 30
This should work. Assumes that the table is named "time_list" and the ids and times are sequential...
SELECT
count(1)
FROM `time_list`
LEFT OUTER JOIN `time_list` AS `a`
ON (`time_list`.`id` = (`a`.`id` + 1))
WHERE (
(to_seconds(`time_list`.`date_time`) - to_seconds(`a`.`date_time`)) / 60 >= 30
OR `a`.`id` IS null);
You can use MySQL variables (SQLFiddle):
SET @x := NULL;
SELECT count(*) FROM (
SELECT @x AS prev, @x:=date_time AS curr
FROM mytable
ORDER BY id
) y
WHERE curr > prev + interval 30 minute
OR curr < prev