here is a query I use that will get data grouped by the last seven days.
Now, beware, if there is no data for any day then no record for that day
will be returned, so you would have to fill the gaps in your code.
select date_trunc('day', event_time) as event_day, count(*)
from event
where event_time between (date_trunc('day', current_timestamp) + '-7
days') AND date_trunc('day', current_timestamp)
group by event_day
There are other goofy things you could do in SQL:
select last_7days.my_day
from (
select date_trunc('day', current_timestamp) as my_day
union
select date_trunc('day', current_timestamp) + '-1 days' as my_day
union
select date_trunc('day', current_timestamp) + '-2 days' as my_day
union
select date_trunc('day', current_timestamp) + '-3 days' as my_day
union
select date_trunc('day', current_timestamp) + '-4 days' as my_day
union
select date_trunc('day', current_timestamp) + '-5 days' as my_day
union
select date_trunc('day', current_timestamp) + '-6 days' as my_day
) as last_7days
;-)
> > I'm trying to generate a list of days between a start
> > date and an end date for use in a query - I would then
> > do an outer join with some other data for graphing
> > purposes as I need all the days with 0 entries.
>
> istm that you would be able to simply qualify your query with a
>
> WHERE your_date_column BETWEEN 'your_start_date" AND 'your_end_date'
>
> You can use GROUP BY/HAVING to get missing results, or just select on
> the NULL columns in the outer join you have already suggested.
>
> If you put the date column in the output also then you would have the
> list available at that point.
>
> If you are using perl and actually want a complete list of the *missing*
> dates, which is of course what you said you wanted, I would suggest
> doing the above query (which can be efficient) and then generate the
> list of missing dates using perl (which is also efficient generating a
> list).
>
> Folks may have other good suggestions too...
>
> hth
>
> - Thomas
>
> ------------------------------
>
