Thread: Mismapping of Mountain Time
uname -a
Linux hemingway 2.6.32-25-server #44-Ubuntu SMP Fri Sep 17 21:13:39 UTC
2010 x86_64 GNU/Linux
date
Thu Mar 3 15:30:17 MST 2011
ls -l localtime
lrwxrwxrwx 1 root root 31 2010-10-18 08:20 localtime ->
/usr/share/zoneinfo/US/Mountain
postgres=# select * from pg_settings where name = 'TimeZone';
-[ RECORD 1 ]---------------------------------------------------------------
name | TimeZone
setting | Navajo
unit |
category | Client Connection Defaults / Locale and Formatting
short_desc | Sets the time zone for displaying and interpreting time stamps.
extra_desc |
context | user
vartype | string
source | command line
min_val |
max_val |
enumvals |
boot_val | UNKNOWN
reset_val | Navajo
sourcefile |
sourceline |
Version 8.4.7.
There is actually a time zone "Navajo", which is a *separate* time zone
from US/Mountain. Ideas on how this happened?
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
Josh Berkus <josh@agliodbs.com> writes:
> ls -l localtime
> lrwxrwxrwx 1 root root 31 2010-10-18 08:20 localtime ->
> /usr/share/zoneinfo/US/Mountain
> postgres=# select * from pg_settings where name = 'TimeZone';
> setting | Navajo
> There is actually a time zone "Navajo", which is a *separate* time zone
> from US/Mountain. Ideas on how this happened?
It's not all that separate: per the Olsen database,
Link America/Denver US/Mountain
Link America/Denver Navajo
Those are all aliases for the exact same timezone behavior, and PG
doesn't have any good way to choose which one you think is preferred.
It looks like it chooses the shortest name ... so in this case you'll
get Navajo. If you don't like that, set the timezone setting
explicitly or via the TZ environment variable for the postmaster.
regards, tom lane
On 3/3/11 2:31 PM, Josh Berkus wrote: > uname -a > Linux hemingway 2.6.32-25-server #44-Ubuntu SMP Fri Sep 17 21:13:39 UTC > 2010 x86_64 GNU/Linux > > date > Thu Mar 3 15:30:17 MST 2011 Also: echo $TZ returns nothing. We've checked several Ubuntu systems, and it seems that Ubuntu does not set $TZ. --Josh Berkus
Josh Berkus <josh@postgresql.org> writes:
> echo $TZ returns nothing. We've checked several Ubuntu systems, and it
> seems that Ubuntu does not set $TZ.
Red Hat doesn't either; I think this is a general habit on Linux
distros.
regards, tom lane
On Thu, Mar 3, 2011 at 5:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Josh Berkus <josh@postgresql.org> writes: >> echo $TZ returns nothing. =C2=A0We've checked several Ubuntu systems, an= d it >> seems that Ubuntu does not set $TZ. > > Red Hat doesn't either; I think this is a general habit on Linux > distros. If you are using glibc, this is expected/normal. http://www.gnu.org/s/libc/manual/html_node/TZ-Variable.html --=20 Jon
Tom Lane <tgl@sss.pgh.pa.us> wrote: > Josh Berkus <josh@agliodbs.com> writes: >> There is actually a time zone "Navajo", which is a *separate* >> time zone from US/Mountain. Ideas on how this happened? > > It's not all that separate: per the Olsen database, > > Link America/Denver US/Mountain > Link America/Denver Navajo Yeah, it gets complicated, since the State of Arizona ignores Daylight Saving Time adjustments. On the other hand, most of Arizona is covered by the Navajo Nation, which *does* observe DST. Except for some government offices on the reservation. And the Hopi Nation goes along with the local state rules, so pay attention. :-/ So, yeah, Navajo is always the same as most of US/Mountain, but the parts in Arizona don't ignore DST like the rest of the state does. -Kevin
> It's not all that separate: per the Olsen database,
>
> Link America/Denver US/Mountain
> Link America/Denver Navajo
>
> Those are all aliases for the exact same timezone behavior, and PG
> doesn't have any good way to choose which one you think is preferred.
> It looks like it chooses the shortest name ... so in this case you'll
> get Navajo. If you don't like that, set the timezone setting
> explicitly or via the TZ environment variable for the postmaster.
What's more my concern is that Ubuntu, Debian and Red Hat do not set
$TZ, so we'll get this kind of behavior on most Linux systems with a
default install of PostgreSQL. Since it's confusing to users (and will
result in other such bug reports and/or complaints), it would be nice to
do something to pick time zones which is more likely to result in
unsurprising values of Show TimeZone.
(This issue was reported by a customer as a bug to us)
I'll give some thought as to how we could do so, and maybe add it to the
TODO list.
--
-- Josh Berkus
PostgreSQL Experts Inc.
http://www.pgexperts.com
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> It's not all that separate: per the Olsen database,
>>
>> Link America/Denver US/Mountain
>> Link America/Denver Navajo
> Yeah, it gets complicated, since the State of Arizona ignores
> Daylight Saving Time adjustments. On the other hand, most of
> Arizona is covered by the Navajo Nation, which *does* observe DST.
> Except for some government offices on the reservation. And the Hopi
> Nation goes along with the local state rules, so pay attention. :-/
> So, yeah, Navajo is always the same as most of US/Mountain, but the
> parts in Arizona don't ignore DST like the rest of the state does.
Well, the Arizona rules are described by the America/Phoenix zone,
which we *can* tell apart from America/Denver because there's an
actual behavioral difference. If the user wanted Arizona rules
and selected US/Mountain, that's their mistake not ours.
regards, tom lane
Josh Berkus <josh@agliodbs.com> writes:
>> It's not all that separate: per the Olsen database,
>>
>> Link America/Denver US/Mountain
>> Link America/Denver Navajo
> What's more my concern is that Ubuntu, Debian and Red Hat do not set
> $TZ, so we'll get this kind of behavior on most Linux systems with a
> default install of PostgreSQL. Since it's confusing to users (and will
> result in other such bug reports and/or complaints), it would be nice to
> do something to pick time zones which is more likely to result in
> unsurprising values of Show TimeZone.
[ shrug... ] The problem here is that "unsurprising" is in the eye of
the beholder. Given the information available from glibc I don't think
there is any portable way to tell what it thinks the true zone name is.
We could twiddle the tiebreaking rule, like say just use strcmp()
without any preference for shorter lengths. In this case that would
give us America/Denver which is probably less surprising than Navajo
to some people. But seeing that the Olsen database contains a very
large and frequently changing set of timezone names, I think anything
we can do here is going to be subject to the principle of conservation
of astonishment. Making it better for US/Mountain is going to make it
worse for somebody else --- in fact, I seem to recall that the
shortest-name tiebreaking rule got there to hide some even more
egregious behavior of the plain strcmp rule (cf commit
2def4552edbe1ada5aa76954997fd3142cb7c271).
The displayed timezone name is cosmetic. Live with it.
regards, tom lane
Josh Berkus <josh@postgresql.org> writes: > echo $TZ returns nothing. We've checked several Ubuntu systems, and it > seems that Ubuntu does not set $TZ. You can tweak that easily by editing /etc/postgresql/8.4/main/environment. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support