Lists: | pgsql-bugs |
---|
From: | chris(dot)travers(at)gmail(dot)com |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #8170: alter user does not accept timestamp output format in certain datestyles and timezones. |
Date: | 2013-05-20 04:51:41 |
Message-ID: | E1UeI4T-0002Jz-9g@wrigleys.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 8170
Logged by: Chris Travers
Email address: chris(dot)travers(at)gmail(dot)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?
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | chris(dot)travers(at)gmail(dot)com |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: BUG #8170: alter user does not accept timestamp output format in certain datestyles and timezones. |
Date: | 2013-05-20 13:06:18 |
Message-ID: | 3193.1369055178@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-bugs |
chris(dot)travers(at)gmail(dot)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