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
time---- Query
Moderator: General Moderators
Re: time---- Query
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........
-
bpat1434
- Forum Newbie
- Posts: 2
- Joined: Tue Nov 15, 2005 12:14 pm
- Location: Baltimore, MD, USA
- Contact:
feyd | Please use
Now, to fetch in groups of minutes, you'd need to do something a little more elaborate like:
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]
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)Code: Select all
SELECT *
FROM `tablename`
WHERE MINUTE(DATE_SUB(NOW(), INTERVAL 1 day)) >= '00'
AND MINUTE(DATE_SUB(NOW, INTERVAL 1 day)) < '10'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]