On 6/1/2014 9:05 AM, Adrian Klaver wrote:
I ask because a look at the PG JDBC code shows this, which did not
show up until Sept 22, 2011. Not sure what release, but it looks like
9.2+:
// Construct and send a startup packet.
String[][] params = {
{ "user", user },
{ "database", database },
{ "client_encoding", "UTF8" },
{ "DateStyle", "ISO" },
{ "extra_float_digits", "2" },
{ "TimeZone",
createPostgresTimeZone() },
/**
* Convert Java time zone to postgres time zone.
* All others stay the same except that GMT+nn changes to GMT-nn and
* vise versa.
*
* @return The current JVM time zone in postgresql format.
*/
private String createPostgresTimeZone() {
String tz = TimeZone.getDefault().getID();
if (tz.length() <= 3 || !tz.startsWith("GMT")) {
return tz;
}
char sign = tz.charAt(3);
String start;
if (sign == '+') {
start = "GMT-";
} else if (sign == '-') {
start = "GMT+";
} else {
// unknown type
return tz;
}
return start + tz.substring(4);
Ahh I see the problem.
From here:
A time zone abbreviation, for example PST. Such a specification merely
defines a particular offset from UTC, in contrast to full time zone
names which can imply a set of daylight savings transition-date rules
as well. The recognized abbreviations are listed in the
pg_timezone_abbrevs view (see Section 47.70). You cannot set the
^^^^^^^^^^^^^^^^^^
configuration parameters TimeZone or log_timezone to a time zone
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
abbreviation, but you can use abbreviations in date/time input values
^^^^^^^^^^^^^
and with the AT TIME ZONE operator.
So:
test=> select version();
version
-----------------------------------------------------------------------------------------------------------------------------
PostgreSQL 9.3.4 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.7.2 20130108 [gcc-4_7-branch revision 195012], 32-bit
(1 row)
test=> set TimeZone='PST';
ERROR: invalid value for parameter "TimeZone": "PST"
test=> set TimeZone='PST8PDT';
SET
The JDBC code above, if I am following correctly, is picking up a
default timezone of 'PST' and then in the first if returning that as
the tz value to SET TimeZone in the startup packet.
Two things.
1) Where is it getting PST from ?
2) Should the driver even be returning an abbreviation given that
Postgres will not accept it as a TimeZone value?
Thanks for the extra help, Adrian.
It led me to investigate the 35 webapps we deploy on Tomcat and I found
2 rogue apps that set their timezone to "PST". Once I fixed these two,
all is working great again.
I guess there's no bug, per se, except in our configuration. Once we
changed it to PST8PDT, all was good again.
Thanks,
David
