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@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
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