Thread: Timezones in 8.2.7
Was there something wrong with the tzdata distributed in 8.2.7?
current_timestamp is still using EST, but the date command run from the
command line is correctly returning EDT
[local]:owl=# select version(), current_timestamp;
version
| now
---------------------------------------------------------------------------------------------------+-------------------------------
PostgreSQL 8.2.7 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.6
20060404 (Red Hat 3.4.6-3) | 2008-03-25 14:33:11.955247-05
(1 row)
Time: 0.584 ms
[local]:owl=# select now();
now
-------------------------------
2008-03-25 14:33:24.291317-05
(1 row)
Joseph S <jks@selectacast.net> writes:
> Was there something wrong with the tzdata distributed in 8.2.7?
> current_timestamp is still using EST, but the date command run from the
> command line is correctly returning EDT
Works for me ... what have you got TimeZone set to?
regards, tom lane
Tom Lane wrote: > Joseph S <jks@selectacast.net> writes: >> Was there something wrong with the tzdata distributed in 8.2.7? >> current_timestamp is still using EST, but the date command run from the >> command line is correctly returning EDT > > Works for me ... what have you got TimeZone set to? > > regards, tom lane > /etc/localtime -> /usr/share/zoneinfo/US/Eastern I just upgraded the tzdata file to 2007k and restarted postgres, but the problem persists.
On Tue, Mar 25, 2008 at 5:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Joseph S <jks@selectacast.net> writes: > > Was there something wrong with the tzdata distributed in 8.2.7? > > current_timestamp is still using EST, but the date command run from the > > command line is correctly returning EDT > > Works for me ... what have you got TimeZone set to? Weirdly, I'm getting the right offsets, but my timezone in pgsql is navajo... For the OS it's America/Denver like I set it. And there's no timezone set in postgresql.conf. My OS is set to Denver, but was set to Chicago before that. Am running ubuntu 7.10. No idea if this is normal. But seeing as this is a laptop and it got the DST change right I'm not really worried about. Just thought I'd post it in this thread in case it looks worse to you than it does to me...
"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> Weirdly, I'm getting the right offsets, but my timezone in pgsql is
> navajo... For the OS it's America/Denver like I set it. And there's
> no timezone set in postgresql.conf.
According to the TZ files, "Navajo" is just an alias for
"America/Denver":
Link America/Denver Navajo
IIRC we have the TZ-intuiting code set to prefer shorter names when it
has two equally good matches to the system's timezone behavior, so
that's why it's picking "Navajo".
regards, tom lane
Joseph S <jks@selectacast.net> writes:
> Tom Lane wrote:
>> Works for me ... what have you got TimeZone set to?
> /etc/localtime -> /usr/share/zoneinfo/US/Eastern
You didn't answer the question: what does "SHOW TimeZone" say?
regards, tom lane
After I sent my last email, a light bulb went off. I remembered a similar problem I had a while ago with parts of postgres not having read permission. Sure enough after I [root@xx /usr/local/pgsql]# chmod -R a+r * then restart postgres everything is fine. This is a IMHO a bug in the install script. All of the postgres install should be world readable, regardless of the umask (or at least provide a warning). Tom Lane wrote: > Joseph S <jks@selectacast.net> writes: >> Tom Lane wrote: >>> Works for me ... what have you got TimeZone set to? > >> /etc/localtime -> /usr/share/zoneinfo/US/Eastern > > You didn't answer the question: what does "SHOW TimeZone" say? > > regards, tom lane
Joseph Shraibman <jks@selectacast.net> writes:
> Sorry, I didn't realize what you were asking.
> [local]:owl=# SHOW TimeZone;
> TimeZone
> ----------
> EST5EDT
> (1 row)
[ squint... ] That should certainly think that we're into DST;
it does here:
regression=# set timezone = 'EST5EDT';
SET
regression=# select now();
now
-------------------------------
2008-03-26 13:25:22.088717-04
(1 row)
The only thing I can think is that the copy of the timezone data file
you have in your Postgres installation is several years old and predates
the law change. Did you check the stuff under $SHAREDIR/timezone?
(pg_config --sharedir will tell you what SHAREDIR is)
regards, tom lane
Sorry, I didn't realize what you were asking. [local]:owl=# SHOW TimeZone; TimeZone ---------- EST5EDT (1 row) Tom Lane wrote: > Joseph S <jks@selectacast.net> writes: >> Tom Lane wrote: >>> Works for me ... what have you got TimeZone set to? > >> /etc/localtime -> /usr/share/zoneinfo/US/Eastern > > You didn't answer the question: what does "SHOW TimeZone" say? > > regards, tom lane
On Mar 26, 2008, at 12:26 PM, Joseph Shraibman wrote: > After I sent my last email, a light bulb went off. I remembered a > similar problem I had a while ago with parts of postgres not having > read permission. Sure enough after I > > [root@xx /usr/local/pgsql]# chmod -R a+r * > > then restart postgres everything is fine. > > This is a IMHO a bug in the install script. All of the postgres > install should be world readable, regardless of the umask (or at > least provide a warning). What? Are you actually saying that a PG data directory should be world readable? That will never happen as that throws out even the most basic security facilities. Erik Jones DBA | Emma® erik@myemma.com 800.595.4401 or 615.292.5888 615.292.0777 (fax) Emma helps organizations everywhere communicate & market in style. Visit us online at http://www.myemma.com
Erik Jones <erik@myemma.com> writes:
> On Mar 26, 2008, at 12:26 PM, Joseph Shraibman wrote:
>> This is a IMHO a bug in the install script. All of the postgres
>> install should be world readable, regardless of the umask (or at
>> least provide a warning).
> What? Are you actually saying that a PG data directory should be
> world readable? That will never happen as that throws out even the
> most basic security facilities.
No, he's complaining that the share/timezone file tree wasn't
world-readable. I think that's plain old pilot error though.
The install script shouldn't be second-guessing the umask it's
given, any more than it second-guesses the file ownerships.
regards, tom lane
Tom Lane wrote: > No, he's complaining that the share/timezone file tree wasn't > world-readable. I think that's plain old pilot error though. > The install script shouldn't be second-guessing the umask it's > given, any more than it second-guesses the file ownerships. > If binaries are made readable and executable despite the umask, then all the files the binaries need to run should be made world readable. Otherwise you set up this situation where something is likely to break in a hard to figure out way.
No, not the pg data directory. The pg binaries. My problem was caused by the timezone files installed under /usr/local/pgsql/share were not world readable due the way I had the umask of root set when I did the compile and install. Erik Jones wrote: > What? Are you actually saying that a PG data directory should be world > readable? That will never happen as that throws out even the most basic > security facilities.