1

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?

user2492064
  • 591
  • 1
  • 8
  • 21
  • 1
    Did you mean 30 seconds? Because your data is surely in seconds. – Prix Aug 24 '13 at 04:07
  • Do you mean first entry? And when you mean "thus the count would be 2" do you mean that you want to limit it to 2 results? Because I don't know how you would choose only id 1 and id 4 since there are others that are more than 30 seconds after id 1. – Mike Aug 24 '13 at 04:10
  • 1
    @Prix ahh i meant minutes, i apologize. I will update immediately. Thanks mike, the table has thousands of rows, so I do not want to limit to two. I also meant minutes... – user2492064 Aug 24 '13 at 04:17
  • Are you only interested in "30 minutes between _this row_ and the _next row_"? For example, in the sample data above only row `id 3` would be a valid result? Would this be a correct assumption? – Tigger Aug 24 '13 at 04:24

3 Answers3

2

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
mvp
  • 111,019
  • 13
  • 122
  • 148
2

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
Alexander Yancharuk
  • 13,817
  • 5
  • 55
  • 55
Dipesh Parmar
  • 27,090
  • 8
  • 61
  • 90
1

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);
Alexander Yancharuk
  • 13,817
  • 5
  • 55
  • 55
zevra0
  • 209
  • 1
  • 5