Parse CE and BCE in dates and times

Folks,

Please find attached a patch to do $Subject. As dates in a fair number
of fields of endeavor are expressed this way, it seems reasonable to
ensure tha we can parse them on input. Making it possible to use them
in output is a more invasive patch, and would involve changes to
to_date and similar that would require careful consideration.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Op 13-06-2022 om 07:51 schreef David Fetter:
> Folks,
>
> Please find attached a patch to do $Subject. As dates in a fair number
> of fields of endeavor are expressed this way, it seems reasonable to
> ensure tha we can parse them on input. Making it possible to use them
> in output is a more invasive patch, and would involve changes to
> to_date and similar that would require careful consideration.

Hi David,

I find some unexpected results:

# select '112-04-30 BC'::date;
date
---------------
0112-04-30 BC
(1 row)

but the same with the ' BCE' suffix seems broken:

# select '112-04-30 BCE'::date;
ERROR: invalid input syntax for type date: "112-04-30 BCE"
LINE 1: select '112-04-30 BCE'::date;

The same goes for '112-04-30 AD' (works) and its CE version (errors out).

Or is this as expected?

Erik Rijkers

>
> Best,
> David.

On Mon, Jun 13, 2022 at 09:11:56AM +0200, Erik Rijkers wrote:
> Op 13-06-2022 om 07:51 schreef David Fetter:
> > Folks,
> >
> > Please find attached a patch to do $Subject. As dates in a fair number
> > of fields of endeavor are expressed this way, it seems reasonable to
> > ensure tha we can parse them on input. Making it possible to use them
> > in output is a more invasive patch, and would involve changes to
> > to_date and similar that would require careful consideration.
>
> Hi David,
>
> I find some unexpected results:
>
> # select '112-04-30 BC'::date;
> date
> ---------------
> 0112-04-30 BC
> (1 row)
>
> but the same with the ' BCE' suffix seems broken:
>
> # select '112-04-30 BCE'::date;
> ERROR: invalid input syntax for type date: "112-04-30 BCE"
> LINE 1: select '112-04-30 BCE'::date;
>
> The same goes for '112-04-30 AD' (works) and its CE version (errors out).
>
> Or is this as expected?

It's not, and thanks for looking at this. Will check to see what's
going on here.

Best,
David.
--
David Fetter <david(at)fetter(dot)org> http://fetter.org/
Phone: +1 415 235 3778

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

This entry has been waiting on author input for a while (our current
threshold is roughly two weeks), so I've marked it Returned with
Feedback.

Once you think the patchset is ready for review again, you (or any
interested party) can resurrect the patch entry by visiting

https://commitfest.postgresql.org/38/3682/

and changing the status to "Needs Review", and then changing the
status again to "Move to next CF". (Don't forget the second step;
hopefully we will have streamlined this in the near future!)

Thanks,
--Jacob