Query

[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.

 

 

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........

 

Between these timestamps there is a records.

I should find the average between each timestamps

 

 

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]

How to find the records between these

[jaymc]

convert those dates into unix timestamps, 1 line of php

 

I used to use your method until I ran into problems

 

You can problably use mysql but much easier to update all those to unix timestamps so you can have more control when searching with time integers

[Illusion]

Finding the records between each 10 mints interval is not a problem , but grouping them to find avg of timestamps with a groupcolumn which has a unique value for each interval is the problem.

 

As we can't loop it for each interval using MySQL better try with PHP.