Re: Getting number of days in a month

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(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005