Skip site navigation (1) Skip section navigation (2)

Re: Messed up time zones

From: JC de Villa <jc(dot)devilla(at)gmail(dot)com>
To: Laszlo Nagy <gandalf(at)shopzeus(dot)com>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Messed up time zones
Date: 2012-08-03 08:58:28
Message-ID: CAOvw+NbbtoN7ehSmnAg=wutMZiimguXb4uYha8OEYE69xyJGTg@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Robert KlemmeDate: 2012-08-03 09:18:20
Subject: Re: query using incorrect index
Previous:From: Laszlo NagyDate: 2012-08-03 08:31:43
Subject: Messed up time zones

pgsql-admin by date

Next:From: Laszlo NagyDate: 2012-08-03 09:18:35
Subject: Re: Messed up time zones
Previous:From: Craig RingerDate: 2012-08-03 08:48:30
Subject: Re: need help to write a function in postgresql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group