MySQL query: get data in specific interval

Question

I have a MySQL Table with one datetime column. I want to prevent that the PHP-script gets to much data. So i'm searching for a solution that a MySql query only selects rows which have a distance of 1 minute or whatever. is there something simple or do i have to code a for-loop with a new mysql query every time.

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

thanks in advance

Answer 1

Try this :

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 datetime rounded to the interval, 1 minute (60 seconds) here.

ii) Gets the top row from each group.

This can be a good sampling criteria for your data. This query can be optimized alot on these points:

i) Put a where clause for a date = REQUIRED DATE, and then do other operations on hour+minutes instead of whole datetime.

ii) If your interval is 1 minute, then substring of the timestamp or date_format can be tried too to round it off to nearest minute.

eg.

SELECT create_time
FROM timeTable
WHERE create_time
IN (

SELECT min( create_time )
FROM timeTable
GROUP BY DATE_FORMAT( `create_time` , 'Y-M-D %H:%i' )
);

Answer 2

Try this

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 desired between the current and previous colDate. The first parameter in TIMESTAMPDIFF determines the unit of time that the interval will use. ex: SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, or YEAR.

@time keeps track of the previous colDate, and it is compared with the current row. If the difference between the previous and current colDate is equal to or greater than the interval, it is included.