Selection by date

[benkyma]

I need to make ten selections. One of all the datetimes in the last 24 hours, the next one from 24 hours to 48 hours, and on back in 24 hour blocks for the last ten days. I've tried various approaches to this, but this is what I currently have:

select title, app_id, count(*) as cnt from app_instances where time between now() -1 day and now()-2 day group by title order by cnt desc

The best way I can think of is to run these queries inside a for loop, substituting out the numbers for incremental variables in PHP to result in 10 seperate selections. Does anyone know the correct syntax for this or maybe have a better way?

[darkagn]

I need to make ten selections. One of all the datetimes in the last 24 hours, the next one from 24 hours to 48 hours, and on back in 24 hour blocks for the last ten days. I've tried various approaches to this, but this is what I currently have:

select title, app_id, count(*) as cnt from app_instances where time between now() -1 day and now()-2 day group by title order by cnt desc

The best way I can think of is to run these queries inside a for loop, substituting out the numbers for incremental variables in PHP to result in 10 seperate selections. Does anyone know the correct syntax for this or maybe have a better way?

In PHP the strtotime and date functions might be useful to you. You can go:

for($i=0; $i<10; $i++)
{
  $startDateTime = date("Y-m-d H:i:s", strtotime("-$i days"));
  $endDateTime = date("Y-m-d H:i:s", strtotime("-{$i+1} days"));
  $query = "select title, app_id, count(*) as cnt from app_instances where time between $startDateTime and $endDateTime group by title order by cnt desc";
}

That may require some tweaking to get it spot on, it's just what I came up with on the spot.

[benkyma]

that's just what I needed, thanks a lot