Bug #897: EXTRACT(EPOCH FROM column): Possible wrong output

Jonas Bentzen (jonas at understroem dot dk) reports a bug with a severity of 3
The lower the number the more severe it is.

Short Description
EXTRACT(EPOCH FROM column): Possible wrong output

Long Description
I'm not sure whether this is actually a bug, but here goes: If you define a column as TIMESTAMP WITHOUT TIME ZONE (or TIMESTAMP(0) WITHOUT TIME ZONE), EXTRACT(EPOCH FROM column) returns a time stamp that is exactly one hour later than the time stamp from a column which contains the same date but is defined WITH TIME ZONE. Please see the example for clarification.

Operating system: Linux
PostgreSQL version: 7.3 and 7.3.2 (compiled from source)

Sample Code
test=> \d datotest
Table "public.datotest"
Column | Type | Modifiers
--------+--------------------------------+-----------
dato | timestamp(0) with time zone |
dato2 | timestamp(0) without time zone |

test=> INSERT INTO datotest VALUES ( CURRENT_TIMESTAMP, CURRENT_TIMESTAMP );
INSERT 16981 1
test=> SELECT dato, dato2, EXTRACT(EPOCH FROM dato) AS timestamp1, EXTRACT(EPOCH FROM dato2) AS timestamp2 FROM datotest;
dato | dato2 | timestamp1 | timestamp2
------------------------+---------------------+------------+------------
2003-02-15 11:03:19+01 | 2003-02-15 11:03:19 | 1045303399 | 1045306999
(1 row)

No file was uploaded with this report

pgsql-bugs(at)postgresql(dot)org writes:
> I'm not sure whether this is actually a bug, but here goes: If you define a column as TIMESTAMP WITHOUT TIME ZONE (or TIMESTAMP(0) WITHOUT TIME ZONE), EXTRACT(EPOCH FROM column) returns a time stamp that is exactly one hour later than the time stamp from a column which contains the same date but is defined WITH TIME ZONE. Please see the example for clarification.

When I do it, I get a value five hours earlier ;-)

I believe what is actually happening is that the
timestamp-without-time-zone value is treated as though it were GMT.
I'm not sure whether to consider that a bug or not.

In most other contexts, we interpret such values as being in local time
(the current server TimeZone) when it's necessary to make a distinction.
Consistency would suggest doing it that way here too, I think.

That would mean that extract(epoch from timestamp) would behave exactly
like extract(epoch from timestamp::timestamptz). To get at the current
behavior, you'd need to do something like extract(epoch from timestamp
at time zone 'gmt').

Is that what we want? Thomas, any opinion here?

regards, tom lane

> Operating system: Linux
> PostgreSQL version: 7.3 and 7.3.2 (compiled from source)

> Sample Code
> test=> \d datotest
> Table "public.datotest"
> Column | Type | Modifiers
> --------+--------------------------------+-----------
> dato | timestamp(0) with time zone |
> dato2 | timestamp(0) without time zone |

> test=> INSERT INTO datotest VALUES ( CURRENT_TIMESTAMP, CURRENT_TIMESTAMP );
> INSERT 16981 1
> test=> SELECT dato, dato2, EXTRACT(EPOCH FROM dato) AS timestamp1, EXTRACT(EPOCH FROM dato2) AS timestamp2 FROM datotest;
> dato | dato2 | timestamp1 | timestamp2
> ------------------------+---------------------+------------+------------
> 2003-02-15 11:03:19+01 | 2003-02-15 11:03:19 | 1045303399 | 1045306999
> (1 row)

> No file was uploaded with this report

I said:
> In most other contexts, we interpret such values as being in local time
> (the current server TimeZone) when it's necessary to make a distinction.
> Consistency would suggest doing it that way here too, I think.

I have applied a patch to make extract(epoch from timestamp) assume that
timestamps without time zone are in local time, and generate the correct
Unix epoch value accordingly. This will appear in 7.3.3.

regards, tom lane