Re: [GENERAL] Days in month query
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:[EMAIL PROTECTED] > 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 <[EMAIL PROTECTED]> > 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: [EMAIL PROTECTED] > > [mailto:[EMAIL PROTECTED] 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 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Days in month query
On Wed, Mar 30, 2005 at 16:45:43 -0700, Mark Fox <[EMAIL PROTECTED]> 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
Re: [GENERAL] 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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED])
---(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 [EMAIL PROTECTED])
Re: [GENERAL] Days in month query
> > 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
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] Days in month query
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 <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED])
>
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Days in month query
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:[EMAIL PROTECTED] 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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED])
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 <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED])
Re: [GENERAL] Days in month query
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: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[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
