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
> select now() at time zone 'Australia/ATC' -- time zone "Australia/ATC" not
> 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
> How should I store the user's preferred time zone, and how am I supposed
> to convert dates into that time zone?
> Sent via pgsql-admin mailing list (pgsql-admin(at)postgresql(dot)org)
> To make changes to your subscription:
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
SELECT * from pg_timezone_names where name = 'EST';
name | abbrev | utc_offset | is_dst
EST | EST | -05:00:00 | f
JC de Villa
In response to
pgsql-performance by date
|Next:||From: Robert Klemme||Date: 2012-08-03 09:18:20|
|Subject: Re: query using incorrect index|
|Previous:||From: Laszlo Nagy||Date: 2012-08-03 08:31:43|
|Subject: Messed up time zones|
pgsql-admin by date
|Next:||From: Laszlo Nagy||Date: 2012-08-03 09:18:35|
|Subject: Re: Messed up time zones|
|Previous:||From: Craig Ringer||Date: 2012-08-03 08:48:30|
|Subject: Re: need help to write a function in postgresql|