Page 1 of 1

time---- Query

Posted: Mon Sep 17, 2007 5:26 am
by sandy1028
Hi,

select * from `tablename` where 'fieldname` < now() and fieldname` > (now()-interval 1 day);

This gives the result of all the records for 24 hours.


Please help me in writing a query to find the average of each ten minutes records in database from current timestamp to previous day

Re: time---- Query

Posted: Mon Sep 17, 2007 5:47 am
by sandy1028
sandy1028 wrote:Hi,

select * from `tablename` where 'fieldname` < now() and fieldname` > (now()-interval 1 day);

This gives the result of all the records for 24 hours.


Please help me in writing a query to find the average of each ten minutes records in database from current timestamp to previous day

The timestamp stored in database is like this:
2007-09-16 19:30:02 |
| 2007-09-16 19:40:02 |
| 2007-09-16 19:50:02 |
| 2007-09-16 20:00:02 |
| 2007-09-16 20:10:02 |
| 2007-09-16 20:20:02 |
| 2007-09-16 20:30:02 |
| 2007-09-16 20:40:02 |
| 2007-09-16 20:50:02 |
| 2007-09-16 21:00:01 |
| 2007-09-16 21:10:02 |
| 2007-09-16 21:30:02 |
| 2007-09-16 21:30:02 |
| 2007-09-16 21:40:02



How to write a query to fetch data between these two 2007-09-16 21:40:02 and 2007-09-16 21:30:02 and
2007-09-16 21:30:02 and 2007-09-16 21:30:02 and so on........

Posted: Mon Sep 17, 2007 7:15 am
by bpat1434
feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


You'd want DATE_SUB SQL function to do that:

[syntax="sql"]SELECT *
FROM `tablename`
WHERE `fieldname` < NOW()
   AND `fieldname` > DATE_SUB(NOW(), INTERVAL 1 day)
Now, to fetch in groups of minutes, you'd need to do something a little more elaborate like:

Code: Select all

SELECT *
FROM `tablename`
WHERE MINUTE(DATE_SUB(NOW(), INTERVAL 1 day)) >= '00'
   AND MINUTE(DATE_SUB(NOW, INTERVAL 1 day)) < '10'
Obviously that would be repeated over and over, but you probably could combine them using the CASE operator to define how to do the math. I'm short on time, but thought I'd offer that up to get you going....


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]