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.
(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
CREATE FUNCTION num_days(int, int) RETURNS float8 AS -- years, months
(($1::text || ''-'' || $2::text || ''-01'')::date
+ ''1 month''::interval
- ''1 day''::interval)) AS days'
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
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.
Michael Klatt <mdklatt(at)ou(dot)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.8455 FAX
> "I'm a great motivator. Everyone says they have to work twice as hard
> when I'm around."
In response to
pgsql-novice by date
|Next:||From: Brett W. McCoy||Date: 2001-12-06 21:53:47|
|Subject: Re: Mirror databases|
|Previous:||From: Leandro Fanzone||Date: 2001-12-06 19:12:36|
|Subject: Mirror databases|