I just came across this error that I need to account for after updating to 
7.3.2:
	Note:  Prior to PostgreSQL 7.3, writing just timestamp was equivalent to 
	timestamp with time zone. This was changed for SQL spec compliance.
(from 
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=datatype-datetime.html)

test=# create table test2 (id serial,date_entered timestamp with time zone);
NOTICE:  CREATE TABLE will create implicit sequence 'test2_id_seq' for SERIAL 
column 'test2.id'
CREATE TABLE
test=# \d test2
                                      Table "public.test2"
    Column    |           Type           |                       Modifiers
--------------+--------------------------+-------------------------------------------------------
 id           | integer                  | not null default 
nextval('public.test2_id_seq'::text)
 date_entered | timestamp with time zone |

test=# insert into test2 values (4,'04/01/2003'::date);
INSERT 145267 1
test=# insert into test2 values (3,'04/01/2003'::timestamp);
INSERT 145268 1
test=# select * from test2;
 id |      date_entered
----+------------------------
  4 | 2003-03-31 23:59:00-08
  3 | 2003-03-31 23:59:00-08

This does not affect tables with 'timestamp without time zone'.  I find it 
interesting that it changes the time to 1 minute before midnight.  Not a big 
change to fix this in scripts, but hopefully others will become aware of this 
if/when they upgrade to 7.3.

-doug


Wed, 2 Apr 2003 11:10:31 -0800
Doug Silver <dsilver@urchin.com>
Timestamp with time zone change (error) in 7.3.2?
Doug Silver <dsilver@urchin.com> writes:
> This does not affect tables with 'timestamp without time zone'.  I find it 
> interesting that it changes the time to 1 minute before midnight.

I can assure you it doesn't do that for other people.  What platform are
you on, how did you build Postgres exactly, and what timezone are you
using?

			regards, tom lane


Wed, 02 Apr 2003 14:35:57 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Timestamp with time zone change (error) in 7.3.2? 
On Wednesday 02 April 2003 11:35 am, Tom Lane wrote:
> Doug Silver <dsilver@urchin.com> writes:
> > This does not affect tables with 'timestamp without time zone'.  I find
> > it interesting that it changes the time to 1 minute before midnight.
>
> I can assure you it doesn't do that for other people.  What platform are
> you on, how did you build Postgres exactly, and what timezone are you
> using?
>
> 			regards, tom lane

# select version();
                               version
---------------------------------------------------------------------
 PostgreSQL 7.3.2 on i386-portbld-freebsd4.7, compiled by GCC 2.95.4

Built from FBSD ports system:
# head config.log
This file contains any messages produced by compilers while
running configure, to aid debugging if configure makes a mistake.

It was created by PostgreSQL configure 7.3.2, which was
generated by GNU Autoconf 2.53.  Invocation command line was

  $ ./configure --with-libraries=/usr/local/lib 
--with-includes=/usr/local/include --enable-nls --with-openssl=/usr/local 
--prefix=/usr/local i386-portbld-freebsd4.6

# show timezone;
 TimeZonetest=# select * from test2;
 id |      date_entered
----+------------------------
  4 | 2003-03-31 23:59:00-08
  3 | 2003-03-31 23:59:00-08
(2 rows)


----------
 unknown

Do I need to set the TZ variable in the pgsql's .cshrc or in the postgres 
startup script?  Wait, that didn't change anything (but please let me know if 
that should be set upon startup):

test=# set TIMEZONE TO 'PST8PDT';
SET
test=# show TIMEZONE;
 TimeZone
----------
 PST8PDT
(1 row)

test=# insert into test2 values (5,'04/01/2003'::timestamp);
INSERT 147308 1
test=# select * from test2;
 id |      date_entered
----+------------------------
  4 | 2003-03-31 23:59:00-08
  3 | 2003-03-31 23:59:00-08
  5 | 2003-03-31 23:59:00-08

-doug


Wed, 2 Apr 2003 12:05:47 -0800
Doug Silver <dsilver@urchin.com>
Re: Timestamp with time zone change (error) in 7.3.2?
Doug Silver <dsilver@urchin.com> writes:
> On Wednesday 02 April 2003 11:35 am, Tom Lane wrote:
>> Doug Silver <dsilver@urchin.com> writes:
>>> This does not affect tables with 'timestamp without time zone'.  I find
>>> it interesting that it changes the time to 1 minute before midnight.
>> 
>> I can assure you it doesn't do that for other people.  What platform are
>> you on, how did you build Postgres exactly, and what timezone are you
>> using?

> [ vanilla-looking details ]

I'm stumped.  You ought to be getting

regression=# select * from test2;
 id |      date_entered
----+------------------------
  4 | 2003-04-01 00:00:00-08
  3 | 2003-04-01 00:00:00-08
(2 rows)

which is what I get on 7.3.2 with timezone set to 'PST8PDT'.  (It might
be worth checking that that string is actually valid in FreeBSD, though.
Try, at the shell,
	export TZ=GMT
	date
	export TZ=PST8PDT
	date
and check that the reported time adjusts to GMT and local time
correctly.)

Do you have the ability/willingness to dig into it with a debugger and
see where the conversion is going wrong?  Alternatively, if you are
willing to provide access to your machine, someone else (like me) could
look into it.

			regards, tom lane


Wed, 02 Apr 2003 15:48:02 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: Timestamp with time zone change (error) in 7.3.2? 
Doug Silver <dsilver@urchin.com> writes:
> [ why does he get ]

test=# select '2003-04-04'::date::timestamptz;
      timestamptz
------------------------
 2003-04-03 23:59:00-05
(1 row)

Doug was kind enough to give me access to his machine (a FreeBSD 4.6
box) to look into it.  The answer is that the timezone tables on this
machine seem to have been built with leap second information; this
causes the results of localtime() and related operations to diverge
from what Postgres is expecting.

What actually happens internally is that localtime() returns the value
2003-04-03 23:59:38-05 (22 seconds off the expected result), but we drop
the seconds part for reasons mentioned in timestamp2tm(), giving the
observed behavior.  I believe that 22 seconds is about right for the
accumulated number of leap seconds since 1970, so I'm, um, leaping to
the conclusion that localtime is doing a leap-second-aware computation.

FreeBSD's "man localtime" points out

STANDARDS
     The asctime(), ctime(), difftime(), gmtime(), localtime(), and mktime()
     functions conform to ISO/IEC 9899:1990 (``ISO C89''), and conform to
     ISO/IEC 9945-1:1996 (``POSIX.1'') provided the selected local timezone
     does not contain a leap-second table (see zic(8)).

We are expecting the POSIX-specified behavior (no accounting for leap
seconds).

Not sure if there's anything much we can do about this except to document
"don't do that".  It seems impractical to make our datetime arithmetic
operations cope with leap-second-aware timekeeping.

One idea that comes to mind is to test for leap-second-aware behavior
(for example, by checking to see that localtime() of a value that should be
exactly midnight is exactly midnight) and complain about it if we find
we are on a leap-second-using machine.  But I'm not sure if it's worth
the trouble.  I'm also not sure exactly where/when to perform this test
--- perhaps when setting a new timezone value?  Comments anyone?

			regards, tom lane


Sat, 05 Apr 2003 13:10:47 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: [NOVICE] Timestamp with time zone change (error) in 7.3.2? 
On Saturday 05 April 2003 10:10 am, Tom Lane wrote:
> Doug Silver <dsilver@urchin.com> writes:
> > [ why does he get ]
>
> test=# select '2003-04-04'::date::timestamptz;
>       timestamptz
> ------------------------
>  2003-04-03 23:59:00-05
> (1 row)
>
> Doug was kind enough to give me access to his machine (a FreeBSD 4.6
> box) to look into it.  The answer is that the timezone tables on this
> machine seem to have been built with leap second information; this
> causes the results of localtime() and related operations to diverge
> from what Postgres is expecting.
>
> What actually happens internally is that localtime() returns the value
> 2003-04-03 23:59:38-05 (22 seconds off the expected result), but we drop
> the seconds part for reasons mentioned in timestamp2tm(), giving the
> observed behavior.  I believe that 22 seconds is about right for the
> accumulated number of leap seconds since 1970, so I'm, um, leaping to
> the conclusion that localtime is doing a leap-second-aware computation.
>
> FreeBSD's "man localtime" points out
>
> STANDARDS
>      The asctime(), ctime(), difftime(), gmtime(), localtime(), and
> mktime() functions conform to ISO/IEC 9899:1990 (``ISO C89''), and conform
> to ISO/IEC 9945-1:1996 (``POSIX.1'') provided the selected local timezone
> does not contain a leap-second table (see zic(8)).
>
> We are expecting the POSIX-specified behavior (no accounting for leap
> seconds).
>
> Not sure if there's anything much we can do about this except to document
> "don't do that".  It seems impractical to make our datetime arithmetic
> operations cope with leap-second-aware timekeeping.
>
> One idea that comes to mind is to test for leap-second-aware behavior
> (for example, by checking to see that localtime() of a value that should be
> exactly midnight is exactly midnight) and complain about it if we find
> we are on a leap-second-using machine.  But I'm not sure if it's worth
> the trouble.  I'm also not sure exactly where/when to perform this test
> --- perhaps when setting a new timezone value?  Comments anyone?
>
> 			regards, tom lane

Hi Tom -

The reason for this discrepancy in localtime is due to a program called 
clockspeed that is running on the machine that automatically keeps track of 
time.  It runs as a daemon and constantly adjusts the clock without the
security concerns of ntpd.  I run it on most of my FreeBSD machines so
that I don't have to worry about syncing the time.

Here is some installation information pertinent to this discussion:
	Clockspeed uses the libtai library, check /usr/ports/devel/libtai
	for more details. TAI time measure is off 22 seconds from UTC time
	measure. Therefore, your system time will show a 22 secs difference
	from your time source after you've installed this port.

To compensate for this, they suggest compiling a special version of localtime 
with that accounted for this: "make -DLEAPSECONDS".  I have seen problems 
with clockspeed on an NFS server to non-FBSD clients where they were 
complaining about the time, but something must have changed from 7.2 ->7.3 to 
cause this problem in the first place.

Thanks for investigating this Tom.

-Doug


Mon, 7 Apr 2003 17:15:07 -0700
Doug Silver <dsilver@urchin.com>
Re: [NOVICE] Timestamp with time zone change (error) in 7.3.2?