Re: Number of days in a month

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
>