Received: from magus.postgresql.org (magus.postgresql.org [87.238.57.229]) by mail.postgresql.org (Postfix) with ESMTP id 8EECB930E1E for ; Fri, 3 Aug 2012 06:18:56 -0300 (ADT) Received: from 10610.x.rootbsd.net ([204.109.60.69] helo=vps001.shopzeus.com) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1SxE20-00054G-I7 for pgsql-admin@postgresql.org; Fri, 03 Aug 2012 09:18:55 +0000 Received: from [192.168.1.105] (localhost [127.0.0.1]) by vps001.shopzeus.com (8.14.3/8.14.3) with ESMTP id q739Id5L059380; Fri, 3 Aug 2012 05:18:39 -0400 (EDT) (envelope-from gandalf@shopzeus.com) Message-ID: <501B976B.8060700@shopzeus.com> Date: Fri, 03 Aug 2012 11:18:35 +0200 From: Laszlo Nagy User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:14.0) Gecko/20120714 Thunderbird/14.0 MIME-Version: 1.0 To: JC de Villa CC: pgsql-admin@postgresql.org Subject: Re: Messed up time zones References: <501B8C6F.6040100@shopzeus.com> In-Reply-To: Content-Type: multipart/alternative; boundary="------------050605020600010806090803" X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201208/19 X-Sequence-Number: 39840 This is a multi-part message in MIME format. --------------050605020600010806090803 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit > > Isn't: > > select now() at time zone 'Australia/ATC' > > supposed to be: > > select now() at time zone 'Australia/ACT' I see now. The abbreviation is usually a time zone name. But to be correct, the time zone name should be used (and not the abbreviation). > > And looking at the pg_timezone_names table for EST, there's only one > entry for EST: > > SELECT * from pg_timezone_names where name = 'EST'; > name | abbrev | utc_offset | is_dst > ------+--------+------------+-------- > EST | EST | -05:00:00 | f Okay, but that is the "name", and not the "abbrev" field. So time zone abbreviations are not unique? Then probably it is my fault - I thought that they will be unique. It is still an interesting question, how others interpret these (non-unique) abbreviations? But I guess that is not related to PostgreSQL so I'm being offtopic here. One last question. Am I right in that PostgreSQL does not handle leap seconds? template1=> set time zone 'UTC'; template1=> select '2008-12-31 23:59:60'::timestamp; timestamp --------------------- 2009-01-01 00:00:00 (1 row) And probably intervals are affected too: template1=> set time zone 'UTC'; template1=> select '2008-12-31 00:00:00'::timestamp + '48 hours'::interval; timestamp --------------------- 2009-01-02 00:00:00 (1 row) Should be '2009-01-01 23:59:59' instead. Thanks, Laszlo --------------050605020600010806090803 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit

Isn't:

select now() at time zone 'Australia/ATC'

supposed to be:

select now() at time zone 'Australia/ACT'

I see now. The abbreviation is usually a time zone name. But to be correct, the time zone name should be used (and not the abbreviation).

And looking at the pg_timezone_names table for EST, there's only one entry for EST:

SELECT * from pg_timezone_names where name = 'EST';

name | abbrev | utc_offset | is_dst

------+--------+------------+--------

EST | EST | -05:00:00 | f

Okay, but that is the "name", and not the "abbrev" field. So time zone abbreviations are not unique? Then probably it is my fault - I thought that they will be unique. It is still an interesting question, how others interpret these (non-unique) abbreviations? But I guess that is not related to PostgreSQL so I'm being offtopic here.

One last question. Am I right in that PostgreSQL does not handle leap seconds?

template1=> set time zone 'UTC';
template1=> select '2008-12-31 23:59:60'::timestamp;
      timestamp
---------------------
2009-01-01 00:00:00
(1 row)

And probably intervals are affected too:

template1=> set time zone 'UTC';
template1=> select '2008-12-31 00:00:00'::timestamp + '48 hours'::interval;
      timestamp
---------------------
2009-01-02 00:00:00
(1 row)

Should be '2009-01-01 23:59:59' instead.

Thanks,

   Laszlo

--------------050605020600010806090803--