Обсуждение: Timestamp with time zone change (error) in 7.3.2?

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

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

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

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