Re: [PATH] Correct negative/zero year in to_date/to_timestamp

On Tue, Feb 23, 2016 at 11:58 AM, Vitaly Burovoy
<vitaly(dot)burovoy(at)gmail(dot)com> wrote:
> Hello, Hackers!
>
> I'm writing another patch and while I was trying to cover corner cases
> I found that to_date and to_timestamp work wrong if year in input
> value is zero or negative:
>
> postgres=# SELECT
> postgres-# y || '-06-01' as src
> postgres-# ,CASE WHEN y>0 THEN ('00'||y||'-06-01') WHEN y<0 THEN
> ('00'||(-y)||'-06-01 BC') END::date
> postgres-# ,to_date(y || '-06-01', 'YYYY-MM-DD')
> postgres-# ,to_timestamp(y || '-06-01', 'YYYY-MM-DD')
> postgres-# FROM (VALUES(2),(1),(0),(-1),(-2))t(y);
> src | date | to_date | to_timestamp
> ----------+---------------+---------------+---------------------------
> 2-06-01 | 0002-06-01 | 0002-06-01 | 0002-06-01 00:00:00+00
> 1-06-01 | 0001-06-01 | 0001-06-01 | 0001-06-01 00:00:00+00
> 0-06-01 | | 0001-06-01 BC | 0001-06-01 00:00:00+00 BC
> -1-06-01 | 0001-06-01 BC | 0002-06-01 BC | 0002-06-01 00:00:00+00 BC
> -2-06-01 | 0002-06-01 BC | 0003-06-01 BC | 0003-06-01 00:00:00+00 BC
> (5 rows)
>
> Zero year (and century) is accepted and negative years differs by 1
> from what they should be.
>
>
> I've written a patch fixes that. With it results are correct:
> postgres=# SELECT
> postgres-# y || '-06-01' as src
> postgres-# ,CASE WHEN y>0 THEN ('00'||y||'-06-01') WHEN y<0 THEN
> ('00'||(-y)||'-06-01 BC') END::date
> postgres-# ,to_date(y || '-06-01', 'YYYY-MM-DD')
> postgres-# ,to_timestamp(y || '-06-01', 'YYYY-MM-DD')
> postgres-# FROM (VALUES(2),(1),(-1),(-2))t(y);
> src | date | to_date | to_timestamp
> ----------+---------------+---------------+---------------------------
> 2-06-01 | 0002-06-01 | 0002-06-01 | 0002-06-01 00:00:00+00
> 1-06-01 | 0001-06-01 | 0001-06-01 | 0001-06-01 00:00:00+00
> -1-06-01 | 0001-06-01 BC | 0001-06-01 BC | 0001-06-01 00:00:00+00 BC
> -2-06-01 | 0002-06-01 BC | 0002-06-01 BC | 0002-06-01 00:00:00+00 BC
> (4 rows)
>
>
> When year "0" is given, it raises an ERROR:
> postgres=# SELECT to_timestamp('0000*01*01', 'YYYY*MM*DD');
> ERROR: invalid input string for "YYYY"
> DETAIL: Year cannot be 0.
>
>
> Also I change behavior for era indicator when negatives century or
> year are given. In such case era indicator is ignored (for me it is
> obvious signs should be OR-ed):
> postgres=# SELECT to_timestamp('-0010*01*01 BC', 'YYYY*MM*DD BC')
> postgres-# ,to_timestamp(' 0010*01*01 BC', 'YYYY*MM*DD BC');
> to_timestamp | to_timestamp
> ---------------------------+---------------------------
> 0010-01-01 00:00:00+00 BC | 0010-01-01 00:00:00+00 BC
> (1 row)
>
>
> Testings, complains, advice, comment improvements are very appreciated.

This seems to be a messy topic. The usage of "AD" and "BC" imply that
TO_DATE is using the anno domini system which doesn't have a year 0,
but in the DATE type perhaps we are using the ISO 8601 model[2] where
1 BC is represented as 0000, leading to the difference of one in all
years before 1 AD?

[1] https://en.wikipedia.org/wiki/Anno_Domini
[2] https://en.wikipedia.org/wiki/ISO_8601#Years

--
Thomas Munro
http://www.enterprisedb.com