Friday, 23 August 2013

Count MySQL Row if datetime Interval

Count MySQL Row if datetime Interval

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". 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) 1 - (date_time) 2013-08-23 00:00:30
(id) 2 - (date_time) 2013-08-23 00:00:45
(id) 3 - (date_time) 2013-08-23 00:01:01
(id) 4 - (date_time) 2013-08-23 00:02:30
(id) 5 - (date_time) 2013-08-23 00:02:45
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? Any guidance
would be excellent! Thank you all!

No comments:

Post a Comment