Re: timezone() with timeofday() converts the wrong direction? - Mailing list pgsql-general

On Sun, Apr 24, 2005 at 07:09:44PM +0200, Karsten Hilbert wrote:
> Isn't the following what you want?
...
> gnumed=> select version();
>                             version
> ---------------------------------------------------------------
>  PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3

That *definitely* isn't anything I'd want.  That version of PostgreSQL
is almost four years old and has serious data loss problems.  Aside
from numerous bug fixes, there have been changes in behavior since
then.

> gnumed=> create teable test (f timestamp with time zone);
> CREATE

Does that really work in 7.1.3?  Trying to create a "teable" fails
with a syntax error in all the versions I tried (7.2.7, 7.3.9,
7.4.7, 8.0.2, all from CVS).  I'll create a "table" instead.

> gnumed=> set time zone '+2:00';
> SET VARIABLE
> gnumed=> show time zone;
> NOTICE:  Time zone is +2:00
> SHOW VARIABLE
> gnumed=> insert into test (f) values (now());
> INSERT 6462134 1
> gnumed=> SELECT * from test;
>            f
> ------------------------
>  2005-04-24 17:06:10+00
> (1 row)
>
> gnumed=> select f, f::timestamp at time zone 'MEZ' from test;
>            f            |        timezone
> ------------------------+------------------------
>  2005-04-24 17:06:10+00 | 2005-04-24 18:06:10+01
> (1 row)

The above example gives different results depending on the version
of PostgreSQL, and none of the versions I tested show output in
different time zones.  The following were all run around 22:25 UTC
or a few minutes later:

7.2.7

  create table test (f timestamp with time zone);
  CREATE
  set time zone '+2:00';
  SET VARIABLE
  show time zone;
  psql:foo.sql:4: NOTICE:  Time zone is '+2:00'
  SHOW VARIABLE
  insert into test (f) values (now());
  INSERT 37830 1
  SELECT * from test;
                 f
  -------------------------------
   2005-04-24 22:25:51.669218+00
  (1 row)

  select f, f::timestamp at time zone 'MEZ' from test;
                 f               |          timezone
  -------------------------------+----------------------------
   2005-04-24 22:25:51.669218+00 | 2005-04-24 23:25:51.669218
  (1 row)

7.3.9

  create table test (f timestamp with time zone);
  CREATE TABLE
  set time zone '+2:00';
  SET
  show time zone;
   TimeZone
  ----------
   +2:00
  (1 row)

  insert into test (f) values (now());
  INSERT 731399 1
  SELECT * from test;
                 f
  -------------------------------
   2005-04-24 22:26:41.733617+00
  (1 row)

  select f, f::timestamp at time zone 'MEZ' from test;
                 f               |           timezone
  -------------------------------+-------------------------------
   2005-04-24 22:26:41.733617+00 | 2005-04-24 21:26:41.733617+00
  (1 row)

7.4.7

  create table test (f timestamp with time zone);
  CREATE TABLE
  set time zone '+2:00';
  SET
  show time zone;
   TimeZone
  ----------
   +2:00
  (1 row)

  insert into test (f) values (now());
  INSERT 450835 1
  SELECT * from test;
                 f
  -------------------------------
   2005-04-24 22:27:25.631466+00
  (1 row)

  select f, f::timestamp at time zone 'MEZ' from test;
                 f               |           timezone
  -------------------------------+-------------------------------
   2005-04-24 22:27:25.631466+00 | 2005-04-24 21:27:25.631466+00
  (1 row)

8.0.2

  create table test (f timestamp with time zone);
  CREATE TABLE
  set time zone '+2:00';
  psql:foo.sql:3: ERROR:  unrecognized time zone name: "+2:00"
  set time zone '+2';
  SET
  show time zone;
   TimeZone
  ----------
   02:00:00
  (1 row)

  insert into test (f) values (now());
  INSERT 0 1
  SELECT * from test;
                f
  ------------------------------
   2005-04-25 00:28:33.34721+02
  (1 row)

  select f, f::timestamp at time zone 'MEZ' from test;
                f               |           timezone
  ------------------------------+------------------------------
   2005-04-25 00:28:33.34721+02 | 2005-04-25 01:28:33.34721+02
  (1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/