Re: timestamp default values

On 8/7/05, Michael Fuhr <mike@fuhr.org> wrote:
> On Sat, Aug 06, 2005 at 10:06:35AM -0400, Tom Lane wrote:
> > Can anyone else duplicate the problem?
>
> I couldn't duplicate the problem in 8.0.2 or in any other version
> from 7.2.8 through HEAD (latest CVS for all).
>
> Brendan, if you execute "\set VERBOSITY verbose" in psql and then
> generate the errors, what's the complete error message?
>
> Tom (or anybody else), could the errors could be due to the "if
> (VARSIZE(str) - VARHDRSZ > MAXDATELEN)" checks in text_date() and
> text_timestamp()?  Could an encoding affect that?  That's why I
> suggested increasing the verbosity: so we could see where the error
> is being raised.
>
> --
> Michael Fuhr
>

Okay, I have some more information on this.

The error only occurs for some very particular outputs of timeofday().
 Namely, Saturdays.

No I'm not kidding.

When I tried to diagnose the problem today (Sun Aug 07), everything worked fine.

I was able to replicate the error, however, by deliberately specifying
yesterday's timeofday() string:

=> select timeofday()::timestamp;
         timeofday
----------------------------
 2005-08-07 12:00:43.668919
(1 row)

=> select 'Sat Aug 06 12:00:43.668919 2005 EST'::timestamp;
ERROR:  22007: invalid input syntax for type timestamp: "Sat Aug 06
12:00:43.668919 2005 EST"
LOCATION:  DateTimeParseError, datetime.c:3333

=> select 'Sat Aug 13 12:00:43.668919 2005 EST'::timestamp;
ERROR:  22007: invalid input syntax for type timestamp: "Sat Aug 13
12:00:43.668919 2005 EST"
LOCATION:  DateTimeParseError, datetime.c:3333

=> select 'Sat Aug 13 12:00:43.668919 2005 CST'::timestamp;
ERROR:  22007: invalid input syntax for type timestamp: "Sat Aug 13
12:00:43.668919 2005 CST"
LOCATION:  DateTimeParseError, datetime.c:3333

=> select 'Sun Aug 07 12:00:43.668919 2005 EST'::timestamp;
         timestamp
----------------------------
 2005-08-07 12:00:43.668919
(1 row)

=> select 'Aug 06 12:00:43.668919 2005 EST'::timestamp;
         timestamp
----------------------------
 2005-08-06 12:00:43.668919
(1 row)

=> select 'Sat Aug 06 12:00:43.668919 2005'::timestamp;
         timestamp
----------------------------
 2005-08-06 12:00:43.668919
(1 row)

From the last few examples, you can see that the conversion succeeds
when "Sat" is present at the start, or when the timezone is present at
the end, but not when both are present, as in the timeofday() output.

Perhaps the parser is treating the string "Sat" as a timezone token?

--
BJ