Thread: 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
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
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 >
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 >
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)
>
> > 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.
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
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:
>
>
>>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)
>>
>>
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
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).
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: > > 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 > > >