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
--

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.

Hi All,

I was in fact out-of-date with patches, and have fixed that - which has
brought the zone files up to date.

Will definitely be looking to upgrade all servers to 8.3 at some stage soon
- all about scheduling outages and testing code before we do...

Thanks for everyone's help appreciate it.

Cheers,

Craig

On Fri, Oct 17, 2008 at 3:29 AM, Scott Marlowe <scott.marlowe@gmail.com>wrote:

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--
Craig Ayliffe