From: | Vitaly Burovoy <vitaly(dot)burovoy(at)gmail(dot)com> |
---|---|
To: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | [PATH] Correct negative/zero year in to_date/to_timestamp |
Date: | 2016-02-22 22:58:26 |
Message-ID: | CAKOSWNmwCH0wx6MApc1A8ww++EQmG07AZ3t6w_XjRrV1xeZpTA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
Attachment | Content-Type | Size |
---|---|---|
negative_years_in_to_date.patch | application/octet-stream | 9.4 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Vitaly Burovoy | 2016-02-22 23:05:17 | Re: [PATH] Correct negative/zero year in to_date/to_timestamp |
Previous Message | Jim Nasby | 2016-02-22 22:49:16 | Re: psql metaqueries with \gexec |