MySQL query: retrieving data at a specific interval

Question

I have a MySQL table with one datetime column. I want a PHP script to get a lot of data. Therefore, I am looking for a solution in which only rows with a length of 1 minute or something else are selected in the MySql query. is there something simple or do I need to encode a for loop each time with a new mysql query.

Example timestamp 2012-09-25 00:00:00--> 2012-09-25 00:00:50 2012-09-25 00:01:23 2012-09-25 00:01:30--> 2012-09-25 00:02:33 2012-09-25 00:02:40 2012-09-25 00:03:01-->i want those 

early

Answer 1

Try

 SET @time := '1000-01-01 00:00:00'; SET @interval := 60; SELECT colDate FROM table WHERE TIMESTAMPDIFF( SECOND, @time, colDate ) >= @interval AND @time := colDate 

How it works.

@interval is the time difference between the current and previous colDate. The first parameter in TIMESTAMPDIFF determines the unit of time during which the interval will be used. ex: SECOND, MINUTES, HOUR, DAY, WEEK, MONTH, QUARTER OR YEAR.

@time tracks the previous colDate and compares with the current line. If the difference between the previous and the current match is equal to or greater than the interval, it is turned on.

Answer 2

Try the following:

 SELECT create_time FROM timeTable WHERE create_time IN ( SELECT min( create_time ) FROM timeTable GROUP BY FROM_UNIXTIME( UNIX_TIMESTAMP( create_time ) - MOD( UNIX_TIMESTAMP( create_time ) , 60 ) ); 

How it works:

i) Groups the table by date rounded to the interval, 1 minute (60 seconds) here.

ii) Gets the top row from each group.

This may be a good selection criterion for your data. This query can be optimized at these points:

i) Put the where clause for the date = REQUIRED DATE, and then do other operations for an hour + minutes instead of an integer datetime.

ii) If your interval is 1 minute, you can adjust the substring of the timestamp or date_format to round it to the nearest minute.

eg.

 SELECT create_time FROM timeTable WHERE create_time IN ( SELECT min( create_time ) FROM timeTable GROUP BY DATE_FORMAT( `create_time` , 'YMD %H:%i' ) ); 

Answer 3

WHERE timestamp LIKE '%:30:00%' you will receive every 30 seconds.

But this will only work if you have single records. If your timestamps don't care evenly, you need to let us know.

EDIT

I think you can find this:

How do you select every nth row from mysql