Re: Meridiem markers (was: [BUGS] Incorrect "invalid AM/PM string" error from to_timestamp)

On Sat, Sep 27, 2008 at 4:25 AM, Brendan Jurd <direvus(at)gmail(dot)com> wrote:
> One way to tidy this up would be to re-implement the meridiem markers
> using the seq_search functions, i.e., make it work like the day and
> month names. This would make it easy to accept any flavour of marker,
> and the error messages thrown for bogus input would then be the same
> as those for bogus day and month names.
>

From the better-late-than-never department, comes a patch to improve
the handling of AM/PM and AD/BC markers in to_timestamp(), and up the
ante on error reporting for various kinds of invalid input.

The only difference as far as parsing goes is that we no longer worry
about the case of AM/PM and AD/BC. As long as you use the right
variation for with/without periods, it will parse.

Internally, the code now uses seq_search to parse the markers, so they
work in much the same way as month and day names do.

I improved the error messages for when a seq_search fails, and when
the code fails to parse an integer. Some examples of the new error
messages:

postgres=# select to_timestamp('Tue 20 Foo 2009 11:39 PM', 'Dy DD Mon
YYYY HH:MI PM');
ERROR: invalid value "Foo" for "Mon"
DETAIL: The given value did not match any of the allowed values for this field.

postgres=# select to_timestamp('Tue 20 Jan 2009 11:39 pn', 'Dy DD Mon
YYYY HH:MI PM');
ERROR: invalid value "PN" for "PM"
DETAIL: The given value did not match any of the allowed values for this field.

postgres=# select to_timestamp('Tue 20 Jan 2009 23:39', 'Dy DD Mon YYYY HH:MI');
ERROR: hour "23" is invalid for the 12-hour clock
HINT: Use the 24-hour clock, or give an hour between 1 and 12.

postgres=# select to_timestamp('Tue 20 Jan 2009 xx:39 pm', 'Dy DD Mon
YYYY HH:MI PM');
ERROR: invalid value "xx" for "HH"
DETAIL: Value must be an integer.

This resolves TODO item "Improve to_timestamp() handling of AM/PM, and
error messages". I've added the patch to the 2009 commitfest.

Cheers,
BJ

doc/src/sgml/func.sgml | 24 +--
src/backend/utils/adt/formatting.c | 235 ++++++++++++++++-----------------
src/test/regress/expected/horology.out | 10 -
src/test/regress/sql/horology.sql | 4
4 files changed, 137 insertions(+), 136 deletions(-)