Thread: Re: [QUESTIONS] select date('now'::datetime+'30 day'::timespan)

I was just using 30 days because that's what the previous fellow used..
it works, but some of the time conversion routines are still messed up.

select date('now'::datetime),date(datetime(date('now'::datetime)));
      date|      date
----------+----------
01-27-1998|01-26-1998
(1 row)

those should be the same correct?  it appears to get messed up in the
datetime(date) conversion..

--brett

On Tue, 27 January 1998, at 08:21:22, Thomas G. Lockhart wrote:

> btw, if you want to add a month, better to use
>
> postgres=> select date( cast 'now'::date as datetime + '1
> month'::timespan);
>       date
> ----------
> 02-27-1998
> (1 row)
>
> (The important thing is the '1 month' time span; ignore the casting
> until v6.3 :)

>  > > 6.2.1...  Is there a particular patch I could apply to get this to
>  > > work?  Oh!  note that this may be an alpha only problem, as my dates
>  > > started off by *years*
>  >
>  > Looks like it is an alpha problem, since my patched v6.2.1 works
>  > correctly also. I can't remember if any of the patches available for
>  > v6.2.1 address a problem like this. Look at the readme on
>
> Yes, it is an alpha problem. I received the message below from
> ebert@pe-muc.de which describes a fix that at least makes this stuff
> work. I thought it would have been reported already to make 6.3. Anyway:

Ah. I knew about that one, but since you only had a one day offset in your
example and said that "dates started off by *years*" I thought you had the
other stuff solved.

Would someone who knows about the Alpha port generate a FAQ_DigitalUnix (or
something with a better name) which summarizes these issues? We can include
it in v6.3 and save others the troubles you have encountered.

I'll be sure to get it into the distribution if someone writes it.

                                               - Tom

> > MB> The initial configuration stage defines HAVE_INT_TIMEZONE in
> > MB> config.h.  However, under Digital UNIX, the symbol timezone is some
> > MB> pointer picked up from somewhere. Interpreting that pointer as an
> > MB> offset adds some ridiculous large number to the resulting times
> > MB> resulting in the bogus datetime values. Rather than hack GNU
> > MB> configure tests properly, I just changed the #define
> > MB> HAVE_INT_TIMEZONE 1 line in include/config.h to /* #undef
> > MB> HAVE_INT_TIMEZONE */ and verified that that fixed the problem. The
> > MB> datetime.sql regression test still coughs up a few differences but
> > MB> it's mostly a second or two of different rather than 21 years. I'll
> > MB> assume that's not important unless I hear to the contrary.
> >
> > Editing config.h and undefining HAVE_INT_TIMEZONE solved the problem for
> > me, too.  (OSF/1 3.0 or 3.2, gcc-2.7.2, postgres-6.2.1).  The only
> > differences on the regression tests datetime, abstime, and tinterval
> > concern mixing up PST and PDT.  That is a lot better than the unpatched
> > postgres 6.2.1.