Re: [BUGS] BUG #8170: alter user does not accept timestamp output format in certain datestyles and timezones.

2013-05-20 Thread Tom Lane 
chris.trav...@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


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

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

2013-05-19 Thread chris . travers 
The following bug has been logged on the website:

Bug reference:  8170
Logged by:  Chris Travers
Email address:  chris.trav...@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=# show timezone;
   TimeZone   
--
 Asia/Jakarta
(1 row)

db=# show datestyle;
   DateStyle   
---
 Postgres, DMY
(1 row)

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

db=# 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=#  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?



-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs