Re: Messed up time zones

On Fri, Aug 3, 2012 at 5:18 PM, Laszlo Nagy <gandalf(at)shopzeus(dot)com> wrote:

>
>
> 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
>
>
>
Well, per the docs at
http://www.postgresql.org/docs/9.1/static/functions-datetime.html, in
parens under timezone:

"Technically, PostgreSQL uses UT1because leap seconds are not handled."

Although there is a footnote on that page that states that:

"60 if leap seconds are implemented by the operating system".

--
JC de Villa