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

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:
> > 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
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>