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.
--
Best regards,
Vitaly Burovoy
negative_years_in_to_date.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
