Thread: BUG #8170: alter user does not accept timestamp output format in certain datestyles and timezones.

The following bug has been logged on the website:

Bug reference:      8170
Logged by:          Chris Travers
Email address:      chris.travers@gmail.com
PostgreSQL version: 9.2.4
Operating system:   Debian Linux
Description:        =


I have a pl/pgsql function which calculates at imestamp and alters a user's
password to be valid for 24 hours pending a password change.  When the
datestyle and timezone are set to certain settings this throws an
exception.

Here is an approximation without plpgsql:

db=3D# show timezone;
   TimeZone   =

--------------
 Asia/Jakarta
(1 row)

db=3D# show datestyle;
   DateStyle   =

---------------
 Postgres, DMY
(1 row)

db=3D# select now();
                 now                 =

-------------------------------------
 Mon 20 May 11:39:24.273508 2013 WIT
(1 row)

db=3D# select 'ALTER USER ' || quote_ident('chris') || ' with valid until '=
 ||
quote_literal(now() + '1 day');
                                ?column?                                =

------------------------------------------------------------------------
 ALTER USER chris with valid until 'Tue 21 May 11:41:14.58554 2013 WIT'
(1 row)

db=3D#  ALTER USER chris with valid until 'Tue 21 May 11:41:14.58554 2013
WIT';
ERROR:  invalid input syntax for type timestamp with time zone: "Tue 21 May
11:41:14.58554 2013 WIT"

This worked before with different timezones with the same datestyle.  Why is
this failing?

chris.travers@gmail.com writes:
> db=# show timezone;
>    TimeZone
> --------------
>  Asia/Jakarta
> (1 row)

> db=# select now();
>                  now
> -------------------------------------
>  Mon 20 May 11:39:24.273508 2013 WIT
> (1 row)

> db=#  ALTER USER chris with valid until 'Tue 21 May 11:41:14.58554 2013 WIT';
> ERROR:  invalid input syntax for type timestamp with time zone: "Tue 21 May
> 11:41:14.58554 2013 WIT"

> This worked before with different timezones with the same datestyle.  Why is
> this failing?

You need to add WIT to the timezone abbreviation list to allow it to be
used as input:
http://www.postgresql.org/docs/9.2/static/datetime-config-files.html

Or perhaps better, use the ISO datestyle to eliminate the whole issue of
timezone abbreviations.

            regards, tom lane