Re: Number of days in a month

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(at)pradeshta(dot)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(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.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
> >