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?
Import Notes
Resolved by subject fallback
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
Show quoted text
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 queryGreetings,
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?
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 queryGreetings,
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)---------------------------
Show quoted text
TIP 5: Have you checked our extensive FAQ?
Import Notes
Resolved by subject fallback
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
Show quoted text
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 queryGreetings,
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?---------------------------(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
Import Notes
Resolved by subject fallback
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:
Show quoted text
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 queryGreetings,
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?---------------------------(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
Show quoted text
-----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 queryGreetings,
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 queryGreetings,
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?