Hopefully I'm understanding your question correctly. If so, maybe
this will do what you are wanting.
First, a couple of questions. Do you have this data in a table
already, and are looking to extract information based on the dates?
Or, are you basically wanting something like a for loop so you can
generate the dates between start and stop values?
If the former, and I understand what you are looking to accomplish,
here's one way to do it:
select timestampfield::date::timestamp as "date", count(*) from table
where timestampfield between start and stop group by "date" order by
"date";
should yield:
YYYY-MM-DD 00:00:00 #
for hours, use :
select (substr(timestampfield, 1, 13) || ':00:00')::timestamp as
"hourly", count(*) from table where timestampfield between start and
stop group by "hourly" order by "hourly";
should yield:
YYYY-MM-DD HH:00:00 #
Of course, this assumes your database already has this information.
i hope this helps.
Greg
On Jun 27, 2005, at 10:30 AM, [EMAIL PROTECTED] wrote:
Hi guys,
I've scoured the date/time functions in the docs as well as
google-grouped as many different combinations as I could think of to
figure this out without asking, but I'm having no luck.
I'd like to make a query that would return a list of every trunc'd
TIMESTAMPs between two dates. For example, I'd want to get a list of
every date_trunc('hour',whatever) between 6-1-2005 and 6-10-2005 and
get a list that looks like:
6-1-2005 00:00:00
6-1-2005 01:00:00
6-1-2005 02:00:00
etc
Conversely, I want to generate a list of every day between two dates,
like:
6-1-2005 00:00:00
6-2-2005 00:00:00
6-3-2005 00:00:00
I know there's gotta be some way to do this in a SELECT function, but
I'm running into a brickwall. I'm trying to take some of my date
handling logic out of code and use the db engine so I can spend less
time developing/maintaining code when mature date handling already
exists in a resource I've already got loaded.
Any thoughts?
---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
