Re: [GENERAL] Upgrading from PG 8.3.3 to 9.3.4 - FATAL: invalid value for parameter "TimeZone": "PST"

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