Re: Days in month query

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(dot)fox(at)gmail(dot)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(at)connx(dot)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(at)postgresql(dot)org
>>>[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Mark Fox
>>>Sent: Wednesday, March 30, 2005 3:07 PM
>>>To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org)
>>
>>
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>