Timestamp with time zone change (error) in 7.3.2?

From: Doug Silver <dsilver(at)urchin(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Timestamp with time zone change (error) in 7.3.2?
Date: 2003-04-02 19:10:31
Message-ID: 200304021110.09873.dsilver@urchin.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-novice

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2003-04-02 19:35:57 Re: Timestamp with time zone change (error) in 7.3.2?
Previous Message Andrew Sullivan 2003-04-02 19:02:26 Re: optimizer cost calculation problem

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2003-04-02 19:35:57 Re: Timestamp with time zone change (error) in 7.3.2?
Previous Message Oliver Elphick 2003-04-02 14:23:57 Re: Postgres logs w/ Logrotate