Skip site navigation (1) Skip section navigation (2)

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

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #897: EXTRACT(EPOCH FROM column): Possible wrong output
Date: 2003-02-15 10:19:13
Message-ID: 20030215101913.82249474E61@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2003-02-15 15:23:11
Subject: Re: Bug #897: EXTRACT(EPOCH FROM column): Possible wrong output
Previous:From: Josh BerkusDate: 2003-02-15 00:47:14
Subject: Re: Function will not back up on 7.2.3

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group