Lists: | pgsql-general |
---|
From: | ben(dot)hallert(at)gmail(dot)com |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Generate a list of (days/hours) between two dates |
Date: | 2005-06-27 17:30:38 |
Message-ID: | 1119893438.380710.219440@f14g2000cwb.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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?
From: | Gnanavel Shanmugam <s(dot)gnanavel(at)inbox(dot)com> |
---|---|
To: | ben(dot)hallert(at)gmail(dot)com, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Generate a list of (days/hours) between two dates |
Date: | 2005-06-28 05:44:52 |
Message-ID: | 97895ECAA64.00000888s.gnanavel@inbox.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
> -----Original Message-----
> From: ben(dot)hallert(at)gmail(dot)com
> Sent: 27 Jun 2005 10:30:38 -0700
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] 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?
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
From: | Gregory Youngblood <pgcluster(at)netio(dot)org> |
---|---|
To: | ben(dot)hallert(at)gmail(dot)com, PostgreSQL General <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Generate a list of (days/hours) between two dates |
Date: | 2005-06-28 05:59:48 |
Message-ID: | F2ED511A-54CC-4C66-ACC0-3E2D1F33F962@netio.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)hallert(at)gmail(dot)com 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
>
From: | Michael Fuhr <mike(at)fuhr(dot)org> |
---|---|
To: | ben(dot)hallert(at)gmail(dot)com |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Generate a list of (days/hours) between two dates |
Date: | 2005-06-28 06:38:43 |
Message-ID: | 20050628063843.GA18810@winnie.fuhr.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Mon, Jun 27, 2005 at 10:30:38AM -0700, ben(dot)hallert(at)gmail(dot)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/
From: | "Ben Hallert" <ben(dot)hallert(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Generate a list of (days/hours) between two dates |
Date: | 2005-06-28 15:43:29 |
Message-ID: | 1119973409.327399.45360@g47g2000cwa.googlegroups.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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!