Re-sending this since I seem to have left out the list itself: On Fri, Aug 3, 2012 at 4:31 PM, Laszlo Nagy <gand...@shopzeus.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@postgresql.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
