Обсуждение: Number of days in a month
Is there a PostgreSQL function to return the number of days in a month? ___________________________________ Michael Klatt University of Oklahoma Environmental Verification and Analysis Center 710 Asp Avenue, Suite 8 Norman, OK 73069 405.447.8412 405.447.8455 FAX http://www.evac.ou.edu/ "I'm a great motivator. Everyone says they have to work twice as hard when I'm around."
Вложения
Michael, > Is there a PostgreSQL function to return the number of days in a > month? No. You could fairly easily write a SQL function to do this, though. The function should do the following: 1. Accept a year and month as input. 2. Create a DATETIME value that is the 1st day of that year and month 3. Add one month to the datetime value. 4. Subtract one day from the datetime value 5. Extract the day of the month from that datetime value. 6. The day of the month is your number of days. For the tools to do this, see: http://www.postgresql.org/idocs/index.php?functions-datetime.html and http://techdocs.postgresql.org/techdocs/faqdatesintervals.php -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete information technology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
What I usually do when I have to figure out this type of thing on the
fly (in whatever programming language) is to jump to the first of the
next month, and then back one day. That gets you the last day of
whatever month you are talking about. Here's an example that figures
the number of days in the current month.
SELECT date_part('day',
(date_part('year', CURRENT_DATE) || '-' ||
date_part('month', CURRENT_DATE) || '-01')::date
+ '1 month'::interval
- '1 day'::interval) AS days;
This looks a little clumsy because I have to create a date for the
first day of the current month by getting the current year and and
month and using the || operator to paste these together in the form:
YYYY-MM-01, and then casting that bit of text as a date.
If this is something you do a lot it would be fairly simple to create
a function that did such a thing. In fact, just for fun I created one
myself. Simply pass in the year and month as integers and you get
your response.
CREATE FUNCTION num_days(int, int) RETURNS float8 AS -- years, months
'SELECT date_part(''day'',
(($1::text || ''-'' || $2::text || ''-01'')::date
+ ''1 month''::interval
- ''1 day''::interval)) AS days'
LANGUAGE 'sql';
And a quick test.
SELECT num_days(2000, 2), num_days(2001, 2), num_days(2002, 3);
num_days | num_days | num_days
----------+----------+----------
29 | 28 | 31
(1 row)
Now, I am not entirely sure that this is the *best* way to go about
this, but it works. Hopefully someone else will point out a better
method if one exists.
Jason
Michael Klatt <mdklatt@ou.edu> writes:
> Is there a PostgreSQL function to return the number of days in a month?
>
> ___________________________________
> Michael Klatt
>
> University of Oklahoma
> Environmental Verification and Analysis Center
> 710 Asp Avenue, Suite 8
> Norman, OK 73069
>
> 405.447.8412
> 405.447.8455 FAX
> http://www.evac.ou.edu/
>
> "I'm a great motivator. Everyone says they have to work twice as hard
> when I'm around."
>
>
you know, there only 12 months in a calendar in any given year, so one
could almost have a static lookup table for a give period - say 10 years
or 20 years which would be only 240 (12 start dates and 12 end dates per
year) or 480 entries in that table which is kept permanently in the
database as a table Month(YearNumber,MonthName, MonthFirstDay
MonthLastDay) or any kind of non-changing permanent solution. I know this
will not work if the date system changes, but it is always a easy thing to
refer to, crude and works and doesn't rely upon the operating
system... then again, you have the PGSQL/BASH interface, why not make use
of the system "date" or system "cal" command ?
-samudra
On 6 Dec 2001, Jason Earl wrote:
>
> What I usually do when I have to figure out this type of thing on the
> fly (in whatever programming language) is to jump to the first of the
> next month, and then back one day. That gets you the last day of
> whatever month you are talking about. Here's an example that figures
> the number of days in the current month.
>
> SELECT date_part('day',
> (date_part('year', CURRENT_DATE) || '-' ||
> date_part('month', CURRENT_DATE) || '-01')::date
> + '1 month'::interval
> - '1 day'::interval) AS days;
>
> This looks a little clumsy because I have to create a date for the
> first day of the current month by getting the current year and and
> month and using the || operator to paste these together in the form:
> YYYY-MM-01, and then casting that bit of text as a date.
>
> If this is something you do a lot it would be fairly simple to create
> a function that did such a thing. In fact, just for fun I created one
> myself. Simply pass in the year and month as integers and you get
> your response.
>
> CREATE FUNCTION num_days(int, int) RETURNS float8 AS -- years, months
> 'SELECT date_part(''day'',
> (($1::text || ''-'' || $2::text || ''-01'')::date
> + ''1 month''::interval
> - ''1 day''::interval)) AS days'
> LANGUAGE 'sql';
>
> And a quick test.
>
> SELECT num_days(2000, 2), num_days(2001, 2), num_days(2002, 3);
> num_days | num_days | num_days
> ----------+----------+----------
> 29 | 28 | 31
> (1 row)
>
> Now, I am not entirely sure that this is the *best* way to go about
> this, but it works. Hopefully someone else will point out a better
> method if one exists.
>
> Jason
>
> Michael Klatt <mdklatt@ou.edu> writes:
>
> > Is there a PostgreSQL function to return the number of days in a month?
> >
> > ___________________________________
> > Michael Klatt
> >
> > University of Oklahoma
> > Environmental Verification and Analysis Center
> > 710 Asp Avenue, Suite 8
> > Norman, OK 73069
> >
> > 405.447.8412
> > 405.447.8455 FAX
> > http://www.evac.ou.edu/
> >
> > "I'm a great motivator. Everyone says they have to work twice as hard
> > when I'm around."
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
Yes, that's a good point. I definitely would listen to that kind of
advice if all of your queries asked for the last day of a month. It
also shouldn't be too hard to store this sort of information in a
small table and modify the function so that it either selects the
information from the table and computes it on the fly if the month in
question isn't in the table (it could even fill the table for you).
Jason
Samudra E Haque <haque@pradeshta.net> writes:
> you know, there only 12 months in a calendar in any given year, so
> one could almost have a static lookup table for a give period - say
> 10 years or 20 years which would be only 240 (12 start dates and 12
> end dates per year) or 480 entries in that table which is kept
> permanently in the database as a table Month(YearNumber,MonthName,
> MonthFirstDay MonthLastDay) or any kind of non-changing permanent
> solution. I know this will not work if the date system changes, but
> it is always a easy thing to refer to, crude and works and doesn't
> rely upon the operating system... then again, you have the
> PGSQL/BASH interface, why not make use of the system "date" or
> system "cal" command ?
>
> -samudra
>
>
> On 6 Dec 2001, Jason Earl wrote:
>
> >
> > What I usually do when I have to figure out this type of thing on the
> > fly (in whatever programming language) is to jump to the first of the
> > next month, and then back one day. That gets you the last day of
> > whatever month you are talking about. Here's an example that figures
> > the number of days in the current month.
> >
> > SELECT date_part('day',
> > (date_part('year', CURRENT_DATE) || '-' ||
> > date_part('month', CURRENT_DATE) || '-01')::date
> > + '1 month'::interval
> > - '1 day'::interval) AS days;
> >
> > This looks a little clumsy because I have to create a date for the
> > first day of the current month by getting the current year and and
> > month and using the || operator to paste these together in the form:
> > YYYY-MM-01, and then casting that bit of text as a date.
> >
> > If this is something you do a lot it would be fairly simple to create
> > a function that did such a thing. In fact, just for fun I created one
> > myself. Simply pass in the year and month as integers and you get
> > your response.
> >
> > CREATE FUNCTION num_days(int, int) RETURNS float8 AS -- years, months
> > 'SELECT date_part(''day'',
> > (($1::text || ''-'' || $2::text || ''-01'')::date
> > + ''1 month''::interval
> > - ''1 day''::interval)) AS days'
> > LANGUAGE 'sql';
> >
> > And a quick test.
> >
> > SELECT num_days(2000, 2), num_days(2001, 2), num_days(2002, 3);
> > num_days | num_days | num_days
> > ----------+----------+----------
> > 29 | 28 | 31
> > (1 row)
> >
> > Now, I am not entirely sure that this is the *best* way to go about
> > this, but it works. Hopefully someone else will point out a better
> > method if one exists.
> >
> > Jason
> >
> > Michael Klatt <mdklatt@ou.edu> writes:
> >
> > > Is there a PostgreSQL function to return the number of days in a month?
> > >
> > > ___________________________________
> > > Michael Klatt
> > >
> > > University of Oklahoma
> > > Environmental Verification and Analysis Center
> > > 710 Asp Avenue, Suite 8
> > > Norman, OK 73069
> > >
> > > 405.447.8412
> > > 405.447.8455 FAX
> > > http://www.evac.ou.edu/
> > >
> > > "I'm a great motivator. Everyone says they have to work twice as hard
> > > when I'm around."
> > >
> > >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >