Обсуждение: Problems with Timezones in Australia

On 16/10/2008, at 7:23 PM, Craig Ayliffe wrote:

> Hi,
>
> I have several Postgres DB's not showing correct daylight savings
> time.
>
> From maillist etc I believe these are patched up to the right levels
> to have the correct time zones - but they don't seem to be working
> still.
>
> The unix date command show the correct dates.
>

Try zdump'ing the PostgreSQL TZ data file. My (Debian Etch) system w/
PostgreSQL 8.1.11 shows:

   tethys:~# date -u
   Thu Oct 16 06:39:05 UTC 2008

   tethys:~# zdump /usr/share/postgresql/8.1/timezone/Australia/ACT
   /usr/share/postgresql/8.1/timezone/Australia/ACT  Thu Oct 16
17:39:09 2008 EST

If your zdump is out (and tbh I've no idea if my TZ data for Au/ACT is
up to date or not, I'm a Kiwi!) you'll need to update your PostgreSQL
TZ files (you can probably grab them from source distribution and drop
them in place).

Cheers!

M.

"Craig Ayliffe" <cayliffe@gmail.com> writes:
> I have several Postgres DB's not showing correct daylight savings time.

> From maillist etc I believe these are patched up to the right levels to have
> the correct time zones - but they don't seem to be working still.

No, you're behind the times: 8.2.4 and 8.1.9 are too old to know about
this year's changes in southeast Australia DST laws.  Which I imagine
is what's biting you.

            regards, tom lane

Tom Lane wrote:
> "Craig Ayliffe" <cayliffe@gmail.com> writes:
>> I have several Postgres DB's not showing correct daylight savings time.
>
>> From maillist etc I believe these are patched up to the right levels to have
>> the correct time zones - but they don't seem to be working still.
>
> No, you're behind the times: 8.2.4 and 8.1.9 are too old to know about
> this year's changes in southeast Australia DST laws.  Which I imagine
> is what's biting you.

A semi-cognitive question.

Doesn't Pg use tzdata (at least that's what it's called on for
Redhat-ian distributions) for it's timezone information?


Rod
--
>
>             regards, tom lane
>

Roderick A. Anderson wrote:

> Doesn't Pg use tzdata (at least that's what it's called on for
> Redhat-ian distributions) for it's timezone information?

Yes.  It ships its own, unless told to use the system copy at build
time.  If it's not, then you must upgrade both copies (the system's and
PG's)

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

"Roderick A. Anderson" <raanders@acm.org> writes:
> Tom Lane wrote:
>> No, you're behind the times: 8.2.4 and 8.1.9 are too old to know about
>> this year's changes in southeast Australia DST laws.  Which I imagine
>> is what's biting you.

> Doesn't Pg use tzdata (at least that's what it's called on for
> Redhat-ian distributions) for it's timezone information?

Yes.  tzdata didn't know about those changes back then, either ;-)

If you meant to say "why aren't we using the system's copy of tzdata",
it's because we need to run on systems that don't have one.  If you are
on a platform that uses the standard "Olsen" tz database and you have
confidence that it will get updated regularly, you can configure PG to
use that copy instead of its built-in copy ... but this isn't the
default.

            regards, tom lane

On Thu, Oct 16, 2008 at 12:23 AM, Craig Ayliffe <cayliffe@gmail.com> wrote:
> Hi,
>
> I have several Postgres DB's not showing correct daylight savings time.
>
> Server 1: postgresql-8.2.4 (Ubuntu 7.04)
> Server2: Postgresql-8.1.9 (on Ubuntu Dapper 6.06.1)

Question: Why are you so far behind on pg updates?

Tom Lane wrote:
> "Roderick A. Anderson" <raanders@acm.org> writes:
>> Tom Lane wrote:
>>> No, you're behind the times: 8.2.4 and 8.1.9 are too old to know
>>> about this year's changes in southeast Australia DST laws.  Which
>>> I imagine is what's biting you.
>
>> Doesn't Pg use tzdata (at least that's what it's called on for
>> Redhat-ian distributions) for it's timezone information?
>
> Yes.  tzdata didn't know about those changes back then, either ;-)
>
> If you meant to say "why aren't we using the system's copy of
> tzdata", it's because we need to run on systems that don't have one.

No criticism intended.  Just trying to understand and find out if I
needed to make changes in my update procedures.

> If you are on a platform that uses the standard "Olsen" tz database
> and you have confidence that it will get updated regularly, you can
> configure PG to use that copy instead of its built-in copy ... but
> this isn't the default.

CentOS 5 -- three, four, or maybe more, updates this year so far.  :-)

Is there a way to determine from a binary install (Devrim GÜNDÜZ's rpms)
if it uses the system timezone data or the build-in copy?  Heck I'll
just look at the src rpm.


Thanks,
Rod
--

On Thu, Oct 16, 2008 at 8:52 AM, Roderick A. Anderson <raanders@acm.org> wrote:
> Tom Lane wrote:
> CentOS 5 -- three, four, or maybe more, updates this year so far.  :-)
>
> Is there a way to determine from a binary install (Devrim GÜNDÜZ's rpms) if
> it uses the system timezone data or the build-in copy?  Heck I'll just look
> at the src rpm.

Centos (i.e RHEL) definitely updates tzdata.  I'm pretty sure the PGDG
rpms use the built in tzdata.

Scott Marlowe wrote:
> On Thu, Oct 16, 2008 at 8:52 AM, Roderick A. Anderson <raanders@acm.org> wrote:
>> Tom Lane wrote:
>> CentOS 5 -- three, four, or maybe more, updates this year so far.  :-)
>>
>> Is there a way to determine from a binary install (Devrim GÜNDÜZ's rpms) if
>> it uses the system timezone data or the build-in copy?  Heck I'll just look
>> at the src rpm.
>
> Centos (i.e RHEL) definitely updates tzdata.  I'm pretty sure the PGDG
> rpms use the built in tzdata.

Thanks Scott.  I was pretty sure of this but I've never had a reason or
excuse to test or even think about it.  Well so far.  Murphy's Law is
bound to come into play real soon.  :-)


Rod
--

On Thu, Oct 16, 2008 at 10:23 AM, Roderick A. Anderson <raanders@acm.org> wrote:
> Scott Marlowe wrote:
>>
>> On Thu, Oct 16, 2008 at 8:52 AM, Roderick A. Anderson <raanders@acm.org>
>> wrote:
>>>
>>> Tom Lane wrote:
>>> CentOS 5 -- three, four, or maybe more, updates this year so far.  :-)
>>>
>>> Is there a way to determine from a binary install (Devrim GÜNDÜZ's rpms)
>>> if
>>> it uses the system timezone data or the build-in copy?  Heck I'll just
>>> look
>>> at the src rpm.
>>
>> Centos (i.e RHEL) definitely updates tzdata.  I'm pretty sure the PGDG
>> rpms use the built in tzdata.
>
> Thanks Scott.  I was pretty sure of this but I've never had a reason or
> excuse to test or even think about it.  Well so far.  Murphy's Law is bound
> to come into play real soon.  :-)

I run pg 8.3.3 (update to 8.3.4 is planned in the next week or so) on
centos 5.2 myself.  While a lot of packages, including other dbs, make
some insane changes mid stream on stable releases, pgsql generally
doesn't.  Big changes only happen when the new major version comes
out, so keeping up to date is a pretty safe bet on pgsql.