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@;sss.pgh.pa.us] Sent: 23 October 2002 19:47 To: Hosen, John Cc: 'Thomas Lockhart'; '[EMAIL PROTECTED]' Subject: Re: [HACKERS] 'epoch'::timestamp and Daylight Savings "Hosen, John" <[EMAIL PROTECTED]> 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/ *********************************************************************** ---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives? http://archives.postgresql.org
