Thread: Problems with Timezones in Australia
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.
Server 1: postgresql-8.2.4 (Ubuntu 7.04)
dbtest=> show timezone;
TimeZone
-----------
localtime
(1 row)
dbtest=> select localtime;
time
-----------------
16:16:32.853566
(1 row)
craiga@xyz-01:~$ date
Thu Oct 16 17:16:36 EST 2008
And another:
Server2: Postgresql-8.1.9 (on Ubuntu Dapper 6.06.1)
btest=> show timezone ;
TimeZone
---------------
Australia/ACT
(1 row)
btest=> select localtime;
time
-----------------
16:17:45.227342
(1 row)
craiga@citadel:~$ date
Thu Oct 16 17:18:31 EST 2008
--
Craig Ayliffe
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.
Server 1: postgresql-8.2.4 (Ubuntu 7.04)
dbtest=> show timezone;
TimeZone
-----------
localtime
(1 row)
dbtest=> select localtime;
time
-----------------
16:16:32.853566
(1 row)
craiga@xyz-01:~$ date
Thu Oct 16 17:16:36 EST 2008
And another:
Server2: Postgresql-8.1.9 (on Ubuntu Dapper 6.06.1)
btest=> show timezone ;
TimeZone
---------------
Australia/ACT
(1 row)
btest=> select localtime;
time
-----------------
16:17:45.227342
(1 row)
craiga@citadel:~$ date
Thu Oct 16 17:18:31 EST 2008
--
Craig Ayliffe
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.
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
--
Craig Ayliffe
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:I run pg 8.3.3 (update to 8.3.4 is planned in the next week or so) on
> 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. :-)
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