Messed up time zones

From: Laszlo Nagy <gandalf(at)shopzeus(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Cc: Daniel Fekete <dani(at)shopzeus(dot)com>
Subject: Messed up time zones
Date: 2012-08-03 08:31:43
Message-ID: 501B8C6F.6040100@shopzeus.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin pgsql-performance

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

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Laszlo Nagy 2012-08-03 08:37:11 Re: need help to write a function in postgresql
Previous Message Scott Marlowe 2012-08-02 16:44:32 Re: VACUUM ANALYZE block the whole database

Browse pgsql-performance by date

  From Date Subject
Next Message JC de Villa 2012-08-03 08:58:28 Re: Messed up time zones
Previous Message Kevin Grittner 2012-08-02 20:13:01 Re: query using incorrect index