Re: Messed up time zones

Re-sending this since I seem to have left out the list itself:

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

> select abbrev,utc_offset,count(*) from pg_timezone_names
> where abbrev='EST'
> group by abbrev,utc_offset
>
> There are 12 times zones with 'EST' code, offset = GMT+10. And there are 8
> time zones with 'EST' code, offset= GMT+5 at the same time!
>
> So how much it is supposed to be?
>
> select now() at time zone 'UTC' - now() at time zone 'EST'
>
> (Actually it returns +5:00 but what is the explanation?)
>
> And how am I supposed to convert a date to Australian zone? This doesn't
> work:
>
> select now() at time zone 'Australia/ATC' -- time zone "Australia/ATC" not
> recognized
>
> Background: we have a site where multiple users are storing data in the
> same database. All dates are stored in UTC, but they are allowed to give
> their preferred time zone as a "user preference". So far so good. The users
> saves the code of the time zone, and we convert all timestamps in all
> queries with their preferred time zone. But we got some complaints, and
> this is how I discovered the problem.
>
> Actually, there are multiple duplications:
>
>
> select abbrev,count(distinct utc_offset)
> from pg_timezone_names
> group by abbrev
> having count(distinct utc_offset)>1
> order by 2 desc
>
>
> "CST";3
> "CDT";2
> "AST";2
> "GST";2
> "IST";2
> "WST";2
> "EST";2
>
>
> How should I store the user's preferred time zone, and how am I supposed
> to convert dates into that time zone?
>
> Thanks,
>
> Laszlo
>
>
> --
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/**mailpref/pgsql-admin<http://www.postgresql.org/mailpref/pgsql-admin>
>

Isn't:

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

supposed to be:

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

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

--
JC de Villa