6

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

simmi91
  • 61
  • 1
  • 7
  • 2
    I don't understand the criteria, why do you want those 3? – CrazyCasta Sep 26 '12 at 05:35
  • You wants to choose every few rows? right? – d-_-b Sep 26 '12 at 05:37
  • it's just an example. I just don't want all values in the time 00:01:00 to 00:02:00, one is enough. in my real table there are for example 10 values in the interval of 1 minute; to decrease the amount of data i just want to select one value. maybe the highest. – simmi91 Sep 26 '12 at 05:37
  • Are there any similarities between the ones you want? Are they all times ending in `00:00` or `30:00` for example? – d-_-b Sep 26 '12 at 05:38
  • no, thought of modulo but didn't get a good solution. this are process information of a machine. every time the piececounter changes this information is added to the database. therefore random times. something like a for-loop in the mysql query would be great – simmi91 Sep 26 '12 at 05:42
  • why haven't u chosen anything between 2 and 3 mintues? – nawfal Sep 26 '12 at 05:42
  • http://stackoverflow.com/questions/858746/how-do-you-select-every-n-th-row-from-mysql – d-_-b Sep 26 '12 at 05:44
  • i can't make use of n-th row as my data is randomly inserted. if the machine has to pause there are no entries and suddenly i get some, which maybe are skipped. – simmi91 Sep 26 '12 at 05:48
  • Something like: select data in timeinterval of 1 minute, and get the newest timestamp value would be a perfect solution. – simmi91 Sep 26 '12 at 05:51

3 Answers3

1

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.

walterquez
  • 349
  • 2
  • 11
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' )
);
DhruvPathak
  • 42,059
  • 16
  • 116
  • 175
0

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

But this will only work if you have uniform entries..if your timestamps dont all end evenly.. you'll need to let us know.

EDIT

I think you may be looking for this:

How do you select every n-th row from mysql

Community
  • 1
  • 1
d-_-b
  • 21,536
  • 40
  • 150
  • 256
  • what if there is nothing like 30 in some interval? – nawfal Sep 26 '12 at 05:41
  • 1
    Something like: select data in timeinterval of 1 minute, and get the newest timestamp value would be a perfect solution, but this for all data in a given time range :P – simmi91 Sep 26 '12 at 05:53
  • to describe the context: i have a page with dygraph-diagrams. if i select a time range of 3 month, the mysql database has around 1 million rows that fit to this time range. this is too much data for a ajax request and not really necessary. i don't need the data of the machine in every second. 1 row per minute would limit the data transfered by ajax and rendered by the graph – simmi91 Sep 26 '12 at 06:00
  • I think you should clarify in your question whether or not the timestamps will be random (i.e. will there be an entry on every minute, or at least every 2 minutes etc...otherwise a solution similar to what I wrote would work – d-_-b Sep 26 '12 at 06:04