Re: '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