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

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

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
>
>