Re: [PATH] Correct negative/zero year in to_date/to_timestamp - Mailing list pgsql-hackers

From Robert Haas
Subject Re: [PATH] Correct negative/zero year in to_date/to_timestamp
Date
Msg-id CA+TgmoYW4=65Z4z=9dZNYWV81DSU+rGaQO=P5t0h-nFZhwJBGw@mail.gmail.com
Whole thread Raw
In response to Re: [PATH] Correct negative/zero year in to_date/to_timestamp  (Thomas Munro <thomas.munro@enterprisedb.com>)
Responses Re: [PATH] Correct negative/zero year in to_date/to_timestamp
List pgsql-hackers
On Tue, Feb 23, 2016 at 6:23 AM, Thomas Munro
<thomas.munro@enterprisedb.com> wrote:
> 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?

Well, the way to figure that out, I think, is to look at the
documentation.  I had a look at...

http://www.postgresql.org/docs/9.5/static/functions-formatting.html

...which says...

YYYY year (4 or more digits)
IYYY ISO 8601 week-numbering year (4 or more digits)

I don't really understand ISO 8601, but if IYYY is giving us an ISO
8601 thing, then presumably YYYY is not supposed to be giving us that. The same page elsewhere refers to Gregorian
dates,and other parts
 
of the documentation seem to agree that's what we use.

But having said that, this is kind of a weird situation.  We're
talking about this:

rhaas=# SELECT y || '-06-01', to_date (y || '-06-01', 'YYYY-MM-DD')
FROM (VALUES (2), (1), (0), (-1), (-2)) t(y);?column? |    to_date
----------+---------------2-06-01  | 0002-06-011-06-01  | 0001-06-010-06-01  | 0001-06-01 BC-1-06-01 | 0002-06-01
BC-2-06-01| 0003-06-01 BC
 
(5 rows)

Now, I would be tempted to argue that passing to_date('-1-06-01',
'YYYY-MM-DD') ought to do the same thing as to_date('pickle',
'YYYY-MM-DD') i.e. throw an error.  There's all kinds of what seems to
me to be shoddy error checking in this area:

rhaas=# select to_date('-3', 'YYYY:MM&DD');   to_date
---------------0004-01-01 BC
(1 row)

It's pretty hard for me to swallow the argument that the input matches
the provided format.

However, I'm not sure we ought to tinker with the behavior in this
area.  If YYYY-MM-DD is going to accept things that are not of the
format YYYY-MM-DD, and I'd argue that -1-06-01 is not in that format,
then I think it should probably keep doing the same things it's always
done.  If you want to supply a BC date, why not do this:

rhaas=# select to_date('0001-06-01 BC', 'YYYY-MM-DD BC');   to_date
---------------0001-06-01 BC
(1 row)

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: PostgreSQL extension API? Documentation?
Next
From: Robert Haas
Date:
Subject: Re: PATCH: index-only scans with partial indexes