Generate a list of (days/hours) between two dates
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?
This might be helpful,
select current_date + s.t as dates from generate_series(0,5) as s(t);
dates
------------
2005-06-28
2005-06-29
2005-06-30
2005-07-01
2005-07-02
2005-07-03
(6 rows)
with regards,
S.Gnanavel
Show quoted text
-----Original Message-----
From: ben.hallert@gmail.com
Sent: 27 Jun 2005 10:30:38 -0700
To: pgsql-general@postgresql.org
Subject: [GENERAL] Generate a list of (days/hours) between two datesHi 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
etcConversely, 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:00I 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?
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, ben.hallert@gmail.com wrote:
Show quoted text
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
etcConversely, 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:00I 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?
On Mon, Jun 27, 2005 at 10:30:38AM -0700, ben.hallert@gmail.com wrote:
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
Something like this?
SELECT '2005-06-01 00:00:00'::timestamp + x * interval'1 hour'
FROM generate_series(0, 9 * 24) AS g(x);
Another possibility would be to write your own set-returning function
that takes the start and end timestamps and a step value.
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
SELECT '2005-06-01 00:00:00'::timestamp + x * interval'1 day'
FROM generate_series(0, 9) AS g(x);
generate_series() is a function in PostgreSQL 8.0 and later, but
it's trivial to write in earlier versions using PL/pgSQL.
http://www.postgresql.org/docs/8.0/static/functions-srf.html
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
Hi,
hier the same for minutes.
Just change the intervall to 'hour' and the series-count to '24' :
select
current_date || ' ' || mytimequery.mytime
as dates
from
(select
(TIME '00:00:00' + myintervalquery.myinterval)::time as mytime
from
(select
(s.t ||' minute')::interval as myinterval
from
generate_series(0,1439) as s(t)
)
as myintervalquery
)
as mytimequery;
Best regards
Hakan Kocaman
Software-Developer
digame.de GmbH
Richard-Byrd-Str. 4-8
50829 Köln
Tel.: +49 (0) 221 59 68 88 31
Fax: +49 (0) 221 59 68 88 98
Email: hakan.kocaman@digame.de
Show quoted text
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of
Gnanavel Shanmugam
Sent: Tuesday, June 28, 2005 7:45 AM
To: ben.hallert@gmail.com; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Generate a list of (days/hours)
between two datesThis might be helpful,
select current_date + s.t as dates from generate_series(0,5) as s(t);
dates
------------
2005-06-28
2005-06-29
2005-06-30
2005-07-01
2005-07-02
2005-07-03
(6 rows)with regards,
S.Gnanavel-----Original Message-----
From: ben.hallert@gmail.com
Sent: 27 Jun 2005 10:30:38 -0700
To: pgsql-general@postgresql.org
Subject: [GENERAL] Generate a list of (days/hours) between two datesHi 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 geta 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
etcConversely, 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:00I 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?
---------------------------(end of
broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
Import Notes
Resolved by subject fallback
Thanks for the replies! I've adopted the generate_series method, it's
absolutely perfect. I didn't have the dates in a table yet, I needed a
method to generate them from scratch, and this will do nicely.
Thanks again, and hopefully I'll be able to contribute back someday!