0

I am logging live data to a database where it is then selected by a php script and posted to a graph on a webpage.

A new datapoint is added every second and I have been graphing the most recent 300 points for a 5 minute history on the graph using the following:

SELECT * FROM table_name ORDER BY timestamp DESC LIMIT 300;

What I would like to do is add functionality to zoom out the data, losing resolution but showing a longer history but selecting every 5th row for example. However if I just select the most recent 300 5th entries the graph 'snakes' across the page as the values I am asking for are different each time as shown:

query 1 entries: [8:00] [7:55] [7:50] [7:45] [7:40] ...
query 2 entries: [8:01] [7:56] [7:51] [7:46] [7:41] ...

where what I want to do is

query 1 entries: [8:00] [7:55] [7:50] [7:45] [7:40] ...
query 1 entries: [8:05] [8:00] [7:55] [7:50] [7:45] ...

I have been looking for a way to do something like

SELECT * FROM table_name WHERE timestamp IS MULTIPLE OF 5 SECONDS

but have had no luck so here I am.

2

something like

select * from table where MOD(UNIX_TIMESTAMP(timestamp),300)) = 0;

should do the job. But I am not sure if this is correct. The idea is to try to modulo divide the minute part evenly. Be aware, that this may cause in a full table scan

0

is your query1 and query2 are query from 1 table only? try to use this if you want to get query2 = query1 value + 5mins

SELECT * FROM table_name where timestamp = query1 + INTERVAL 5 MINUTE;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.