Thread: BUG #3511: timeofday()::timestamp failing on Saturday when using australian_timezones = on

The following bug has been logged online:

Bug reference:      3511
Logged by:          Adam
Email address:      adam@subdesigns.net
PostgreSQL version: 8.1.9
Operating system:   Debian etch, using postgresql-8.1:8.1.9-0etch1
Description:        timeofday()::timestamp failing on Saturday when using
australian_timezones = on
Details:

Conversion to a timestamp is failing to decode a timeofday() date string
which has both 'Sat' and a timezone 'EST', when australian_timezones is
enabled.

date config is set as:
datestyle = 'iso, dmy'
australian_timezones = on

Following code details the conversion issue. Removal of the 'EST' timezone,
or changing to a different (even invalid) day does not result in an error.

erams=# select timeofday();
              timeofday
-------------------------------------
 Sat Aug 04 12:17:29.290100 2007 EST
(1 row)

erams=# select timeofday()::timestamp;
ERROR:  invalid input syntax for type timestamp: "Sat Aug 04 12:17:29.290100
2007 EST"
erams=# select 'Sat Aug 04 12:17:29.290100 2007 EST'::timestamp;
ERROR:  invalid input syntax for type timestamp: "Sat Aug 04 12:17:29.290100
2007 EST"
erams=# select 'Sun Aug 05 12:17:29.290100 2007 EST'::timestamp;
        timestamp
--------------------------
 2007-08-05 12:17:29.2901
(1 row)

erams=# select 'Sat Aug 11 12:17:29.290100 2007 EST'::timestamp;
ERROR:  invalid input syntax for type timestamp: "Sat Aug 11 12:17:29.290100
2007 EST"
erams=# select 'Sun Aug 11 12:17:29.290100 2007 EST'::timestamp;
        timestamp
--------------------------
 2007-08-11 12:17:29.2901
(1 row)

erams=# select 'Sat Aug 10 12:17:29.290100 2007 EST'::timestamp;
ERROR:  invalid input syntax for type timestamp: "Sat Aug 10 12:17:29.290100
2007 EST"
erams=# select 'Sat Aug 09 12:17:29.290100 2007 EST'::timestamp;
ERROR:  invalid input syntax for type timestamp: "Sat Aug 09 12:17:29.290100
2007 EST"
erams=# select 'Sat Aug 04 12:17:29.290100 2007'::timestamp;
        timestamp
--------------------------
 2007-08-04 12:17:29.2901
(1 row)

erams=# select 'Mon Aug 04 12:17:29.290100 2007 EST'::timestamp;
        timestamp
--------------------------
 2007-08-04 12:17:29.2901
(1 row)

Conversion to timestamptz also results in same issue:

erams=# select timeofday()::timestamptz;
ERROR:  invalid input syntax for type timestamp with time zone: "Sat Aug 04
12:52:47.381035 2007 EST"


Removal of the 'Sat' corrects the issue:
erams=# select substring(timeofday() from '^[^[:space:]]* (.*
[[:digit:]]{4})')::timestamp;
         substring
----------------------------
 2007-08-04 13:33:20.825623
(1 row)

erams=# select substring(timeofday() from '^[^[:space:]]* (.*
[[:digit:]]{4})')::timestamp::timestamptz
erams-# ;
           substring
-------------------------------
 2007-08-04 13:33:26.137588+10
(1 row)


Changing australian_timezones = off corrects the issue.

"Adam" <adam@subdesigns.net> writes:
> Conversion to a timestamp is failing to decode a timeofday() date string
> which has both 'Sat' and a timezone 'EST', when australian_timezones is
> enabled.

Yeah, there's not a lot we can do about that.  Changing timeofday()'s
output format seems a bad idea, because the only reason that function
exists at all is for backwards compatibility.  And the fact that SAT
means something else in australian_timezones mode is unfortunate, but
there's not much to be done about it either.

As of 8.2 you can use clock_timestamp() to get the number you want
without any textual conversions.  I'd recommend that ...

            regards, tom lane