Patch for PKST timezone

Hi all,

Please accept attached patch for the following problem.

aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$
aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$ ./psql postgres
psql (9.0beta1)
Type "help" for help.

postgres=# SHOW timezone;
TimeZone
--------------
Asia/Karachi
(1 row)

postgres=#
postgres=# CREATE TABLE test_table (c1 INT, c2 TIMESTAMP DEFAULT
timeofday()::TIMESTAMP);
CREATE TABLE
postgres=# INSERT INTO test_table VALUES (1);
ERROR: invalid input syntax for type timestamp: "Fri Apr 30 15:36:43.906075
2010 PKST"
postgres=#

And here is a little bit information about the system I am using.

aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$ uname -a
Linux aftab-laptop 2.6.31-20-generic #58-Ubuntu SMP Fri Mar 12 05:23:09 UTC
2010 i686 GNU/Linux
aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$
aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$ ./pg_config
--version
PostgreSQL 9.0beta1
aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$

Thanks,

--
Aftab Hussain,
EntepriseDB http://www.enterprisedb.com

Aftab Hussain <aftab(dot)se(at)gmail(dot)com> wrote:

> Please accept attached patch for the following problem.

This patch has not been replied, but I can reproduce the error with:
=# SET timezone = 'Asia/Karachi';
=# SELECT timeofday()::timestamptz;
ERROR: invalid input syntax for type timestamp with time zone: "Tue May 11 13:26:53.264293 2010 PKST"

Should we add PKST timezone? Also, I found a list of timezones[1]
and we don't have 36 tznames in the list. Should we also need them?

ACDT, AEDT, AWDT
BIT
CBT, CDBT, CIST
HMT
PKST, PMT
R*T and R*DT series,
WCDT, WCT, WIB, WITA, WKT

[1] http://www.world-time-zones.org/zones/

> aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$
> aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$ ./psql postgres
> psql (9.0beta1)
> Type "help" for help.
>
> postgres=# SHOW timezone;
> TimeZone
> --------------
> Asia/Karachi
> (1 row)
>
> postgres=#
> postgres=# CREATE TABLE test_table (c1 INT, c2 TIMESTAMP DEFAULT
> timeofday()::TIMESTAMP);
> CREATE TABLE
> postgres=# INSERT INTO test_table VALUES (1);
> ERROR: invalid input syntax for type timestamp: "Fri Apr 30 15:36:43.906075
> 2010 PKST"
> postgres=#
>
> And here is a little bit information about the system I am using.
>
> aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$ uname -a
> Linux aftab-laptop 2.6.31-20-generic #58-Ubuntu SMP Fri Mar 12 05:23:09 UTC
> 2010 i686 GNU/Linux
> aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$
> aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$ ./pg_config
> --version
> PostgreSQL 9.0beta1
> aftab(at)aftab-laptop:/opt/dev/pgsql/install/dbserver/bin$

Regards,
---
Takahiro Itagaki
NTT Open Source Software Center

Takahiro Itagaki wrote:
> Aftab Hussain <aftab(dot)se(at)gmail(dot)com> wrote:
>
>> Please accept attached patch for the following problem.
>
> This patch has not been replied, but I can reproduce the error with:
> =# SET timezone = 'Asia/Karachi';
> =# SELECT timeofday()::timestamptz;
> ERROR: invalid input syntax for type timestamp with time zone: "Tue May 11 13:26:53.264293 2010 PKST"
>
> Should we add PKST timezone? Also, I found a list of timezones[1]
> and we don't have 36 tznames in the list. Should we also need them?

I don't think we want to include all timezone names in the default
config, timezone abbreviations aren't always unique for example. But we
should include PKST because we already include PKT; it would be nasty
for an application to work during winter, and stop working when the
daylight saving time begins.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Takahiro Itagaki wrote:
>> Should we add PKST timezone? Also, I found a list of timezones[1]
>> and we don't have 36 tznames in the list. Should we also need them?

> I don't think we want to include all timezone names in the default
> config, timezone abbreviations aren't always unique for example. But we
> should include PKST because we already include PKT; it would be nasty
> for an application to work during winter, and stop working when the
> daylight saving time begins.

I don't think that's actually it. It looks to me like the Asia/Karachi
zone definition expects the abbreviations to be PKST and PKDT. Not sure
whether PKT is really in use, but it is not sensible to add PKST by
itself.

regards, tom lane

Tom Lane wrote:
> Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
>> Takahiro Itagaki wrote:
>>> Should we add PKST timezone? Also, I found a list of timezones[1]
>>> and we don't have 36 tznames in the list. Should we also need them?
>
>> I don't think we want to include all timezone names in the default
>> config, timezone abbreviations aren't always unique for example. But we
>> should include PKST because we already include PKT; it would be nasty
>> for an application to work during winter, and stop working when the
>> daylight saving time begins.
>
> I don't think that's actually it. It looks to me like the Asia/Karachi
> zone definition expects the abbreviations to be PKST and PKDT. Not sure
> whether PKT is really in use, but it is not sensible to add PKST by
> itself.

How did you come to that conclusion? Googling for "Karachi PKDT"
produces no hits that would suggest PKDT to be a valid timezone name.
This (http://www.speaking-clock.com/Asia-Pakistan-Karachi_212.html) and
this
(http://www.timeanddate.com/worldclock/clockchange.html?n=757&year=2009)
also agree that PKT is the abbreviation for the winter time and PKST for
the summer time.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> writes:
> Tom Lane wrote:
>> I don't think that's actually it. It looks to me like the Asia/Karachi
>> zone definition expects the abbreviations to be PKST and PKDT. Not sure
>> whether PKT is really in use, but it is not sensible to add PKST by
>> itself.

> How did you come to that conclusion?

Er ... misreading the zone definition, that's how. Nevermind.

regards, tom lane

On Tue, May 11, 2010 at 10:45 AM, Heikki Linnakangas
<heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
> I don't think we want to include all timezone names in the default
> config, timezone abbreviations aren't always unique for example. But we
> should include PKST because we already include PKT; it would be nasty
> for an application to work during winter, and stop working when the
> daylight saving time begins.

I agree with everything Heikki says. As the original author of the
timezone files I guess that I am to blame for not having included PKST
in the first place. However I have the excuse that it was like that
already back when the timezone information was still hardcoded, we had
"pkt" but not "pkst":

http://anoncvs.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/datetime.c?rev=1.168;content-type=text%2Fx-cvsweb-markup

Good we have found that inconsistency now, so let's add PKST.

I also agree with not adding more than necessary to the default config
set. Given that so few people complain about it we seem to have a good
working default set already.

Joachim

Joachim Wieland <joe(at)mcknight(dot)de> writes:
> Good we have found that inconsistency now, so let's add PKST.

OK, done. BTW, I notice that PKT was labeled "(not in zic)", which
is not the case, per this discussion. I seem to recall having noticed
some others that seemed to be mislabeled the same way. What process did
you use to compare this list to the zic files, and do we need to revisit
it?

regards, tom lane

On Wed, May 12, 2010 at 12:39 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Joachim Wieland <joe(at)mcknight(dot)de> writes:
>> Good we have found that inconsistency now, so let's add PKST.
>
> OK, done.  BTW, I notice that PKT was labeled "(not in zic)", which
> is not the case, per this discussion.  I seem to recall having noticed
> some others that seemed to be mislabeled the same way.  What process did
> you use to compare this list to the zic files, and do we need to revisit
> it?

I have used a modified version of zic.c that outputs the data while
generating the binary timezone files. Generating the timezone offset
files from that then included some scripts and some manual work. It
seems that we should have an automated process for that, at least for
checking against our current set. I'll see if I can come up with that.
PKST for example was valid only for a single year in the past but in
the newer timezone data it is now valid forever. Ideally we can run a
script that tells us about such changes whenever we bring in new
timezone data.

Joachim