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

pgsql-bugs@xxxxxxxxxxxxxx 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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html