Re: Days in month query - Mailing list pgsql-general

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/static/functions-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
>