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