Postgres Pro
Downloads
Home   >   mailing lists

Thread: Getting number of days in a month

Getting number of days in a month

From
Guillaume Perréal
Date:
To obtain the number of days in a month, I wrote this function:

CREATE FUNCTION dayCountOfMonth(datetime) RETURNS float AS
'    DECLARE
        theDate        ALIAS FOR $1;
        monthStart    date;
        monthEnd    date;
    BEGIN
        monthStart := DATE_TRUNC(''month'', theDate);
        monthEnd := monthStart + ''1 month''::timespan - ''1 day''::timespan;
        RETURN DATE_PART(''doy'', monthEnd) - DATE_PART(''doy'', monthStart) + 1;
    END;
'     LANGUAGE 'PL/pgSQL';

It seems to work, except with the month of October (10).

dayCountOfMonth('1997-10-1') => 30
dayCountOfMonth('1998-10-1') => 30
dayCountOfMonth('1999-10-1') => 31
dayCountOfMonth('2000-10-1') => 30
dayCountOfMonth('2001-10-1') => 30
dayCountOfMonth('2002-10-1') => 30
dayCountOfMonth('2003-10-1') => 30
dayCountOfMonth('2004-10-1') => 31

Just one question: WHY??????
(Note: no trouble with February)

Is there a function that give the number of days of a month?

Thanks,

Guillaume Perréal - Stagiaire MIAG
Cemagref (URH), Lyon, France
Tél: (+33) 4.72.20.87.64

Re: Getting number of days in a month

From
Ken Causey
Date:
Here's my perl implementation:

#######################################
#
#  lastday( month, year (4 digit) )
#
#  Returns: last day of the month
#
#######################################

sub lastday {
   my $month=shift;
   my $year= shift;

   $month--;

   my @days = (31,0,31,30,31,30,31,31,30,31,30,31);

   if($days[$month] != 0){
      return $days[$month];
   } else {
   # It's Feb, test for leap year
      if($year % 4 != 0){
         return 28;
      } elsif($year % 400 == 0){
         return 29;
      } elsif($year % 100 == 0){
         return 28;
      } else {
         return 29;
      }
   }
}

Ken Causey
ineffable

At 02:26 PM 4/12/00 +0200, you wrote:
>To obtain the number of days in a month, I wrote this function:
>
>CREATE FUNCTION dayCountOfMonth(datetime) RETURNS float AS
>'    DECLARE
>        theDate        ALIAS FOR $1;
>        monthStart    date;
>        monthEnd    date;
>    BEGIN
>        monthStart := DATE_TRUNC(''month'', theDate);
>        monthEnd := monthStart + ''1 month''::timespan - ''1 day''::timespan;
>        RETURN DATE_PART(''doy'', monthEnd) - DATE_PART(''doy'', monthStart) + 1;
>    END;
>'     LANGUAGE 'PL/pgSQL';
>
>It seems to work, except with the month of October (10).
>
>dayCountOfMonth('1997-10-1') => 30
>dayCountOfMonth('1998-10-1') => 30
>dayCountOfMonth('1999-10-1') => 31
>dayCountOfMonth('2000-10-1') => 30
>dayCountOfMonth('2001-10-1') => 30
>dayCountOfMonth('2002-10-1') => 30
>dayCountOfMonth('2003-10-1') => 30
>dayCountOfMonth('2004-10-1') => 31
>
>Just one question: WHY??????
>(Note: no trouble with February)
>
>Is there a function that give the number of days of a month?
>
>Thanks,
>
>Guillaume Perréal - Stagiaire MIAG
>Cemagref (URH), Lyon, France
>Tél: (+33) 4.72.20.87.64
>
>

Re: Getting number of days in a month

From
Guillaume Perréal
Date:
Ken Causey wrote:
>
> Here's my perl implementation:
>
> #######################################
> #
> #  lastday( month, year (4 digit) )
> #
> #  Returns: last day of the month
> #
> #######################################
>
> sub lastday {
>    my $month=shift;
>    my $year= shift;
>
>    $month--;
>
>    my @days = (31,0,31,30,31,30,31,31,30,31,30,31);
>
>    if($days[$month] != 0){
>       return $days[$month];
>    } else {
>    # It's Feb, test for leap year
>       if($year % 4 != 0){
>          return 28;
>       } elsif($year % 400 == 0){
>          return 29;
>       } elsif($year % 100 == 0){
>          return 28;
>       } else {
>          return 29;
>       }
>    }
> }
>
> Ken Causey
> ineffable
>
> At 02:26 PM 4/12/00 +0200, you wrote:
> >To obtain the number of days in a month, I wrote this function:
> >
> >CREATE FUNCTION dayCountOfMonth(datetime) RETURNS float AS
> >'      DECLARE
> >               theDate         ALIAS FOR $1;
> >               monthStart      date;
> >               monthEnd        date;
> >       BEGIN
> >               monthStart := DATE_TRUNC(''month'', theDate);
> >               monthEnd := monthStart + ''1 month''::timespan - ''1 day''::timespan;
> >               RETURN DATE_PART(''doy'', monthEnd) - DATE_PART(''doy'', monthStart) + 1;
> >       END;
> >'      LANGUAGE 'PL/pgSQL';
> >
> >It seems to work, except with the month of October (10).
> >
> >dayCountOfMonth('1997-10-1') => 30
> >dayCountOfMonth('1998-10-1') => 30
> >dayCountOfMonth('1999-10-1') => 31
> >dayCountOfMonth('2000-10-1') => 30
> >dayCountOfMonth('2001-10-1') => 30
> >dayCountOfMonth('2002-10-1') => 30
> >dayCountOfMonth('2003-10-1') => 30
> >dayCountOfMonth('2004-10-1') => 31
> >
> >Just one question: WHY??????
> >(Note: no trouble with February)
> >
> >Is there a function that give the number of days of a month?
> >
> >Thanks,
> >
> >Guillaume Perréal - Stagiaire MIAG
> >Cemagref (URH), Lyon, France
> >Tél: (+33) 4.72.20.87.64
> >
> >

Thanks, I rewrote my function to solve my problem.

In fact, the problem is that ('2000-10-01'::datetime + '1 month'::interval)
gives '2000-10-31' instead of '2000-11-01'.
I think it's a bug, isn't it?

Guillaume Perréal - Stagiaire MIAG
Cemagref (URH), Lyon, France
Tél: (+33) 4.72.20.87.64

Re: Getting number of days in a month

From
Patrick Welche
Date:
On Thu, Apr 13, 2000 at 09:24:36AM +0200, Guillaume Perréal wrote:
>
> Thanks, I rewrote my function to solve my problem.
>
> In fact, the problem is that ('2000-10-01'::datetime + '1 month'::interval)
> gives '2000-10-31' instead of '2000-11-01'.
> I think it's a bug, isn't it?

It's our old friend daylight savings changeover:

rfb=# select ('2000-10-01'::datetime + '1 month'::interval);
        ?column?
------------------------
 2000-10-31 23:00:00+00
(1 row)
            ^^

1 hour less because going from summer -> winter (For me BST->GMT)

Cheers,

Patrick

Re: Getting number of days in a month

From
"Ross J. Reedstrom"
Date:
On Thu, Apr 13, 2000 at 10:26:17AM +0100, Patrick Welche wrote:
> On Thu, Apr 13, 2000 at 09:24:36AM +0200, Guillaume Perréal wrote:
> >
> > Thanks, I rewrote my function to solve my problem.
> >
> > In fact, the problem is that ('2000-10-01'::datetime + '1 month'::interval)
> > gives '2000-10-31' instead of '2000-11-01'.
> > I think it's a bug, isn't it?
>
> It's our old friend daylight savings changeover:
>
> rfb=# select ('2000-10-01'::datetime + '1 month'::interval);
>         ?column?
> ------------------------
>  2000-10-31 23:00:00+00
> (1 row)
>             ^^
>
> 1 hour less because going from summer -> winter (For me BST->GMT)
>

Right - and it's 'fixable' by setting your timezone to GMT before doing the
math:

reedstrm=> set timezone to 'GMT';
SET VARIABLE
reedstrm=> select ('2000-10-01'::datetime + '1 month'::interval);
?column?
----------------------------
Wed Nov 01 00:00:00 2000 GMT
(1 row)

Hmm, now that I've said that, I discover that I can't set my timezone back
to the previous behavior: if I set the timezone to anything, it treats
all date values as being in that timezone, and the math just works,
even when I set it to 'unknown'. Hmm, I'll have to test 7.0beta5 for this.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St.,  Houston, TX 77005