Re: 'epoch'::timestamp and Daylight Savings

Thomas & Tom,

Thanks for all your help, I have updated all the defaults to use timestamptz
and everything is working fine again.

We can't wait for 7.3, keep up the good work.

John

-----Original Message-----
From: Tom Lane [mailto:tgl(at)sss(dot)pgh(dot)pa(dot)us]
Sent: 23 October 2002 19:47
To: Hosen, John
Cc: 'Thomas Lockhart'; 'pgsql-hackers(at)postgresql(dot)org'
Subject: Re: [HACKERS] 'epoch'::timestamp and Daylight Savings

"Hosen, John" <John(dot)Hosen(at)capita(dot)co(dot)uk> writes:

> e_app_print_date | timestamp with time zone | default
> "timestamp"('epoch'::text)

Yeah, there's your problem. You are casting 'epoch' to type timestamp
without time zone, and thence to timestamp with time zone. The first step
gives "midnight" and the second assumes that that means "midnight local
time".

For example, in US EST zone I get:

regression=# select "timestamp"('epoch'::text);
timestamp
---------------------
1970-01-01 00:00:00
(1 row)

regression=# select ("timestamp"('epoch'::text))::timestamptz;
timestamp
------------------------
1970-01-01 00:00:00-05
(1 row)

whereas what is wanted is

regression=# select "timestamptz"('epoch'::text);
timestamptz
------------------------
1969-12-31 19:00:00-05
(1 row)

So you can fix the problem just by setting the default to be
'epoch'::timestamptz.

The problem is probably related to the renaming we've been carrying out to
get closer to the SQL spec: "timestamp" now means timestamp without time
zone, which is not what it meant in older Postgres releases.

regards, tom lane

This email has been scanned for all viruses by the MessageLabs SkyScan
service.

***********************************************************************

This email and any files attached to it are confidential and intended
solely for the use of the individual or entity to whom they are
addressed. If you have received this email in error please notify
the system manager.

The message and any files attached to it have been scanned by
MIMEsweeper with Sophos Sweep and found to be free from all known
viruses.

Information on MIMEsweeper can be found at http://www.mimesweeper.com/

***********************************************************************