Thread: Singapore time zone
We seem to be having a time zone problem on a box configured for Singapore timezone. The OS is Suse10.2 PostgreSQL version is 8.1.5.
This simple PHP file prints 2008-05-21 23:04:24SGT
<?php
$d = date('Y-m-d H:i:sT', time());
print $d;
?>
Testing that timestamp string in PostgreSQL fails. It works ok with the other time zone.
host=> SELECT CAST('2008-05-21 23:04:24SST' AS TIMESTAMP WITH TIME ZONE);
timestamptz
------------------------
2008-05-22 05:04:24+08
(1 row)
host=> SELECT CAST('2008-05-21 23:04:24SGT' AS TIMESTAMP WITH TIME ZONE);
ERROR: invalid input syntax for type timestamp with time zone: "2008-05-21 23:04:24 SGT"
Is this something that’s been fixed in a later version or is it some other issue?
Thanks.
"Wright, George" <George.Wright@infimatic.com> writes:
> We seem to be having a time zone problem on a box configured for
> Singapore timezone. The OS is Suse10.2 PostgreSQL version is 8.1.5.
> Testing that timestamp string in PostgreSQL fails. It works ok with the
> other time zone.
It looks to me like 8.1 thinks SST is Swedish Summer Time (GMT+2),
so for your purposes that probably only "works" for rather small
values of "works". 8.1 doesn't know the SGT timezone abbreviation
at all.
In 8.1 there is no way to change this behavior short of compiling
your own version with a patched timezone abbreviations table.
You might consider updating to 8.2 or later, in which versions
the set of recognized TZ abbreviations is user-configurable.
FWIW, 8.4 and beyond will have SGT = GMT-8 in the default abbreviation
list.
regards, tom lane