|
Home > Archive > PostgreSQL Discussion > March 2005 > Days in month query
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Days in month query
|
|
| Mark Fox 2005-03-30, 7:04 pm |
| Greetings,
This is more of an SQL question, but since each database server seems
to use it's own syntax for handling dates...
Is there a way to query for the days in a month? For example,
querying for the days in January of this year? Listing the days
between two dates would be useful as well.
I'm sure I saw a query like this somewhere, but I can't track it down.
Just to be clear, there were no tables involved. Just a SELECT
statement that returned all the days in a given month.
Basically, I have a table of "events" and I'd like to generate a
histogram of how many events occur on the days of a particular month.
What I do now is create a temporary table, fill it with the
appropriate days, and then do a cross join and summation to generate
what I need. This works, but seems messy to me.
Mark
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
| |
| Dann Corbit 2005-03-30, 7:04 pm |
| The online documentation has a search function. It would lead you to
this:
http://www.postgresql.org/docs/8.0/...s-datetime.html
-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mark Fox
Sent: Wednesday, March 30, 2005 3:07 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Days in month query
Greetings,
This is more of an SQL question, but since each database server seems
to use it's own syntax for handling dates...
Is there a way to query for the days in a month? For example,
querying for the days in January of this year? Listing the days
between two dates would be useful as well.
I'm sure I saw a query like this somewhere, but I can't track it down.
Just to be clear, there were no tables involved. Just a SELECT
statement that returned all the days in a given month.
Basically, I have a table of "events" and I'd like to generate a
histogram of how many events occur on the days of a particular month.
What I do now is create a temporary table, fill it with the
appropriate days, and then do a cross join and summation to generate
what I need. This works, but seems messy to me.
Mark
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly
| |
| Mark Fox 2005-03-30, 7:04 pm |
| Greetings,
Thanks Dan, but I searched for, and scoured, that page before asking
my question. It helped with some of the details, but not on the
general approach. I'll try to restate my problem in a better way:
What I want is SELECT statement that references no tables but returns
the days in a given month. I'm now thinking that I might be able to
come up with something using an IN clause and using EXTRACT, but
haven't figured it out yet.
Mark
On Wed, 30 Mar 2005 15:16:19 -0800, Dann Corbit <DCorbit@connx.com> wrote:
> The online documentation has a search function. It would lead you to
> this:
> http://www.postgresql.org/docs/8.0/...s-datetime.html
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mark Fox
> Sent: Wednesday, March 30, 2005 3:07 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Days in month query
>
> Greetings,
>
> This is more of an SQL question, but since each database server seems
> to use it's own syntax for handling dates...
>
> Is there a way to query for the days in a month? For example,
> querying for the days in January of this year? Listing the days
> between two dates would be useful as well.
>
> I'm sure I saw a query like this somewhere, but I can't track it down.
> Just to be clear, there were no tables involved. Just a SELECT
> statement that returned all the days in a given month.
>
> Basically, I have a table of "events" and I'd like to generate a
> histogram of how many events occur on the days of a particular month.
> What I do now is create a temporary table, fill it with the
> appropriate days, and then do a cross join and summation to generate
> what I need. This works, but seems messy to me.
>
> Mark
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)
| |
| Dann Corbit 2005-03-30, 7:04 pm |
| How about create type with create function?
Make an array type to hold the 12 different month day counts.
Give the function year and month as input.
Use this to figure out if it is a leap year:
(year % 4 == 0 && (year % 100 != 0 || year % 400 == 0))
If it is a leap year, then add 1 to days if month is 2.
-----Original Message-----
From: Mark Fox [mailto:mark.fox@gmail.com]
Sent: Wednesday, March 30, 2005 3:46 PM
To: Dann Corbit
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Days in month query
Greetings,
Thanks Dan, but I searched for, and scoured, that page before asking
my question. It helped with some of the details, but not on the
general approach. I'll try to restate my problem in a better way:
What I want is SELECT statement that references no tables but returns
the days in a given month. I'm now thinking that I might be able to
come up with something using an IN clause and using EXTRACT, but
haven't figured it out yet.
Mark
On Wed, 30 Mar 2005 15:16:19 -0800, Dann Corbit <DCorbit@connx.com>
wrote:
> The online documentation has a search function. It would lead you to
> this:
> http://www.postgresql.org/docs/8.0/...s-datetime.html
>
> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Mark Fox
> Sent: Wednesday, March 30, 2005 3:07 PM
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Days in month query
>
> Greetings,
>
> This is more of an SQL question, but since each database server seems
> to use it's own syntax for handling dates...
>
> Is there a way to query for the days in a month? For example,
> querying for the days in January of this year? Listing the days
> between two dates would be useful as well.
>
> I'm sure I saw a query like this somewhere, but I can't track it down.
> Just to be clear, there were no tables involved. Just a SELECT
> statement that returned all the days in a given month.
>
> Basically, I have a table of "events" and I'd like to generate a
> histogram of how many events occur on the days of a particular month.
> What I do now is create a temporary table, fill it with the
> appropriate days, and then do a cross join and summation to generate
> what I need. This works, but seems messy to me.
>
> Mark
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)
| |
| Arthur Hoogervorst 2005-03-30, 7:04 pm |
| Hi,
Something like this?
SELECT date_part('day',
(date_part('year', '01/10/04' :: date) || '-' ||
date_part('month', '01/10/04' :: date) || '-01') ::date
+ '1 month'::interval
- '1 day'::interval) AS days;
Regards,
Arthur
On Wed, 30 Mar 2005 16:45:43 -0700, Mark Fox <mark.fox@gmail.com> wrote:
> Greetings,
>
> Thanks Dan, but I searched for, and scoured, that page before asking
> my question. It helped with some of the details, but not on the
> general approach. I'll try to restate my problem in a better way:
>
> What I want is SELECT statement that references no tables but returns
> the days in a given month. I'm now thinking that I might be able to
> come up with something using an IN clause and using EXTRACT, but
> haven't figured it out yet.
>
> Mark
>
>
> On Wed, 30 Mar 2005 15:16:19 -0800, Dann Corbit <DCorbit@connx.com> wrote:
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)
>
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
| |
| Mike Nolan 2005-03-30, 7:04 pm |
| > > What I want is SELECT statement that references no tables but returns[color=darkre
d]
I have a 'last_day' function (duplicating what the equivalent Oracle
function does), from that you can extract the number of days in the month.
Here's my 'last_day' function:
create or replace function public.last_day(date)
returns date as
'
DECLARE
this_day alias for $1;
declare wk_day date;
BEGIN
wk_day := date_trunc(''month''
, this_day) + interval ''1 month''
- interval ''1 day'';
return wk_day;
END
' language 'plpgsql';
--
Mike Nolan
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
| |
| Jeffrey Melloy 2005-03-31, 7:01 am |
| Or
select date_part('day', date_trunc('month', '01/10/04') + '1
month'::interval - '1 day'::interval) as days;
or
select date_part('day', to_date('mon', 'Jan') + '1 month'::interval - '1
day'::interval) as days;
Arthur Hoogervorst wrote:
>Hi,
>
>Something like this?
>
>SELECT date_part('day',
> (date_part('year', '01/10/04' :: date) || '-' ||
> date_part('month', '01/10/04' :: date) || '-01') ::date
> + '1 month'::interval
> - '1 day'::interval) AS days;
>
>
>
>Regards,
>
>
>Arthur
>
>On Wed, 30 Mar 2005 16:45:43 -0700, Mark Fox <mark.fox@gmail.com> wrote:
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere
" to majordomo@postgresql
.org)
| |
| Bruno Wolff III 2005-03-31, 8:03 pm |
| On Wed, Mar 30, 2005 at 16:45:43 -0700,
Mark Fox <mark.fox@gmail.com> wrote:
>
> What I want is SELECT statement that references no tables but returns
> the days in a given month. I'm now thinking that I might be able to
> come up with something using an IN clause and using EXTRACT, but
> haven't figured it out yet.
You can use the output of the suggested functions as input to
generate_series functions (new in 8.0) and add their output to the
start date of a month, to get a set of dates (as opposed to just a first
and last day of month).
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
| |
| Mark Fox 2005-03-31, 8:03 pm |
| Greetings,
Thanks Dann, Arthur, Mike, Jeffrey, and Bruno. You've given me a
quick solution and a whole lot to chew on. I never would have come up
with anything as creative.
Thanks again,
Mark
> -----Original Message-----
> From: Mark Fox [mailto:mark.fox@gmail.com]
> Sent: Wednesday, March 30, 2005 3:46 PM
> To: Dann Corbit
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Days in month query
>
> Greetings,
>
> Thanks Dan, but I searched for, and scoured, that page before asking
> my question. It helped with some of the details, but not on the
> general approach. I'll try to restate my problem in a better way:
>
> What I want is SELECT statement that references no tables but returns
> the days in a given month. I'm now thinking that I might be able to
> come up with something using an IN clause and using EXTRACT, but
> haven't figured it out yet.
>
> Mark
>
> On Wed, 30 Mar 2005 15:16:19 -0800, Dann Corbit <DCorbit@connx.com>
> wrote:
> broadcast)---------------------------
>
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql
.org
|
|
|
|
|