time---- Query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sandy1028
Forum Commoner
Posts: 60
Joined: Thu Jul 26, 2007 3:56 am

time---- Query

Post 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
sandy1028
Forum Commoner
Posts: 60
Joined: Thu Jul 26, 2007 3:56 am

Re: time---- Query

Post 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........
bpat1434
Forum Newbie
Posts: 2
Joined: Tue Nov 15, 2005 12:14 pm
Location: Baltimore, MD, USA
Contact:

Post 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]
Post Reply