Skip site navigation (1) Skip section navigation (2)

Re: 6.5.0 datetime bug?

From: Thomas Lockhart <lockhart(at)alumni(dot)caltech(dot)edu>
To: gravity(at)dds(dot)nl
Cc: Maarten Boekhold <maarten(dot)boekhold(at)tibcofinance(dot)com>, "Ross J(dot) Reedstrom" <reedstrm(at)wallace(dot)ece(dot)rice(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: 6.5.0 datetime bug?
Date: 2000-03-31 16:54:40
Message-ID: 38E4D850.AD9E146D@alumni.caltech.edu (view raw or flat)
Thread:
Lists: pgsql-hackers
> > Why does the Netherlands (or at least my RH5.2 timezone database)
> > think you switch to DST on March 26?
> Hmmmm, maybe because we actually switched on march 26? In fact, whole of
> europe did AFAIK....

How quaint ;) The US switches this next weekend, which pushes it into
April. So it didn't occur to me that it was a DST issue at first.

And, I got off on the wrong track suggesting a solution. Having a
date_part() which works on dates explicitly doesn't really address the
issue, since you are trying to do the date_part() on a time interval,
not on an absolute date. And the time interval probably *should* keep
track of hours etc. 

However, we *do* have an explicit subtraction operator for dates,
which returns a difference in days, which may be what you want:

postgres=# select '3-27-2000'::date-'3-6-2000'::date as days;
 days 
------
   21
(1 row)

Or, force the type of the timestamp field to be date:

postgres=# select
date('3-27-2000'::timestamp)-date('3-6-2000'::timestamp) as days;
 days 
------
   21
(1 row)

And, if you still want to do the arithmetic using timestamps, you can
force the evaluation of the input into the *same* timezone, as in this
example:

postgres=# select date_part('day',
 '3-27-2000 CET'::timestamp-'3-6-2000 CET'::timestamp) as days;
 days 
------
   21
(1 row)

I'm no longer thinking that an explicit date_part() for date or time
types will be useful.

HTH

                         - Thomas

-- 
Thomas Lockhart				lockhart(at)alumni(dot)caltech(dot)edu
South Pasadena, California

In response to

pgsql-hackers by date

Next:From: Billy G. AllieDate: 2000-03-31 19:39:14
Subject: int8.c compile problem on UnixWare 7.x
Previous:From: Maarten BoekholdDate: 2000-03-31 15:38:03
Subject: Re: 6.5.0 datetime bug?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group