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

Re: Bug #672: timestamp() converts timezone in wrong direction

From: Edwin Grubbs <egrubbs(at)rackspace(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug #672: timestamp() converts timezone in wrong direction
Date: 2002-07-22 18:02:27
Message-ID: Pine.LNX.4.44.0207221242410.32431-100000@zamboni.wc6.rackspace.com (view raw or flat)
Thread:
Lists: pgsql-bugs
I didn't see any follow-up to this bug. The problem is that the 
"datetime"/"timestamp" functions do not calculate the date from the 
seconds since the unix epoch correctly. In bug #695, Tom Lane states that 
the "timestamp" type is moving in the direction of timestamp without 
timezone. It appears that the seconds from the epoch are being converted 
to GMT twice in a row. As you can see from the example, when the timezone 
offset is "-05", the difference between timestamp and timestamptz is 10 
hours. When the offset is "-06", the difference is 12 hours.

This example is from a RedHat Linux 6.2 box with postgresql 7.2.1, kernel
2.4.7 SMP, and glibc-2.1.3-22.

test=# \d test_table
          Table "test_table"
 Column |  Type   | Modifiers 
--------+---------+-----------
 val    | integer | 

test=# set timezone = 'US/Central';
SET VARIABLE
test=# \x
Expanded display is on.
test=# select val, datetime(val), "timestamp"(val), timestamptz(val)           
core-# from test_table order by val;
-[ RECORD 1 ]-----------------------
val         | 0
timestamp   | 1970-01-01 06:00:00
timestamp   | 1970-01-01 06:00:00
timestamptz | 1969-12-31 18:00:00-06
-[ RECORD 2 ]-----------------------
val         | 1
timestamp   | 1970-01-01 06:00:01
timestamp   | 1970-01-01 06:00:01
timestamptz | 1969-12-31 18:00:01-06
-[ RECORD 3 ]-----------------------
val         | 315554400
timestamp   | 1980-01-01 12:00:00
timestamp   | 1980-01-01 12:00:00
timestamptz | 1980-01-01 00:00:00-06
-[ RECORD 4 ]-----------------------
val         | 631173600
timestamp   | 1990-01-01 12:00:00
timestamp   | 1990-01-01 12:00:00
timestamptz | 1990-01-01 00:00:00-06
-[ RECORD 5 ]-----------------------
val         | 646808400
timestamp   | 1990-07-01 10:00:00
timestamp   | 1990-07-01 10:00:00
timestamptz | 1990-07-01 00:00:00-05
-[ RECORD 6 ]-----------------------
val         | 946706400
timestamp   | 2000-01-01 12:00:00
timestamp   | 2000-01-01 12:00:00
timestamptz | 2000-01-01 00:00:00-06
-[ RECORD 7 ]-----------------------
val         | 1027190680
timestamp   | 2002-07-20 23:44:40
timestamp   | 2002-07-20 23:44:40
timestamptz | 2002-07-20 13:44:40-05

test=# set timezone = 'US/Pacific';
SET VARIABLE
test=# select val, datetime(val), "timestamp"(val), timestamptz(val)
core-# from test_table order by val;
-[ RECORD 1 ]-----------------------
val         | 0
timestamp   | 1970-01-01 08:00:00
timestamp   | 1970-01-01 08:00:00
timestamptz | 1969-12-31 16:00:00-08
-[ RECORD 2 ]-----------------------
val         | 1
timestamp   | 1970-01-01 08:00:01
timestamp   | 1970-01-01 08:00:01
timestamptz | 1969-12-31 16:00:01-08
-[ RECORD 3 ]-----------------------
val         | 315554400
timestamp   | 1980-01-01 14:00:00
timestamp   | 1980-01-01 14:00:00
timestamptz | 1979-12-31 22:00:00-08
-[ RECORD 4 ]-----------------------
val         | 631173600
timestamp   | 1990-01-01 14:00:00
timestamp   | 1990-01-01 14:00:00
timestamptz | 1989-12-31 22:00:00-08
-[ RECORD 5 ]-----------------------
val         | 646808400
timestamp   | 1990-07-01 12:00:00
timestamp   | 1990-07-01 12:00:00
timestamptz | 1990-06-30 22:00:00-07
-[ RECORD 6 ]-----------------------
val         | 946706400
timestamp   | 2000-01-01 14:00:00
timestamp   | 2000-01-01 14:00:00
timestamptz | 1999-12-31 22:00:00-08
-[ RECORD 7 ]-----------------------
val         | 1027190680
timestamp   | 2002-07-21 01:44:40
timestamp   | 2002-07-21 01:44:40
timestamptz | 2002-07-20 11:44:40-07

test=# 


-Edwin Grubbs


On Wed, 22 May 2002 pgsql-bugs(at)postgresql(dot)org wrote:

> Simon Kirby (sim(at)netnation(dot)com) reports a bug with a severity of 2
> The lower the number the more severe it is.
> 
> Short Description
> timestamp() converts timezone in wrong direction
> 
> Long Description
> timestamp() and extracting epoch from dates is totally broken:                                                   
>                                                                                                                  
> db=# select "timestamp"(0);                                                                                      
>       timestamp                                                                                                  
> ---------------------                                                                                            
>  1970-01-01 08:00:00                                                                                             
> (1 row)                                                                                                          
>                                                                                                                  
> This should be the same as "perl -le 'print scalar localtime(0)'", which                                         
> prints:                                                                                                          
>                                                                                                                  
> Wed Dec 31 16:00:00 1969                                                                                         
>                                                                                                                  
> db=# select extract(epoch from "timestamp"(0));                                                                  
>  date_part                                                                                                       
> -----------                                                                                                      
>      28800                                                                                                       
> (1 row)                                                                                                          
>                                                                                                                  
> timestamps should never be stored with timezones because that would                                              
> remove the ability to describe times which overlap in daylight savings                                           
> transitions.  It looks like timestamp() is converting in the wrong                                               
> direction, and extract() is as well.                                                                             
> 
> Sample Code
> 
> 
> No file was uploaded with this report
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org
> 
> 





In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-07-22 20:39:21
Subject: Re: Postgres throwing exception for int8 datatype.
Previous:From: Daniel AlvarezDate: 2002-07-22 16:10:39
Subject: pg_ctl -w option does not behave as documented

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