sql: fractional years in intervals should be truncated at months

[jordanlewis]

In Postgres, it appears that specifying an interval with a fractional year quantity returns an interval whose precision is at best "months":

jordan=# select '1 year'::interval;
 interval
----------
 1 year
(1 row)

jordan=# select '1.5 year'::interval;
   interval
---------------
 1 year 6 mons
(1 row)

jordan=# select '1.99 year'::interval;
    interval
----------------
 1 year 11 mons
(1 row)

jordan=# select '1.99999 year'::interval;
    interval
----------------
 1 year 11 mons
(1 row)

In Cockroach, we do not truncate the precision, giving a "more accurate looking" but incompatible-with-Postgres response:

root@:26257/defaultdb> select '1 year'::interval;
  interval
------------
  1 year
(1 row)

Time: 0ms total (execution 0ms / network 0ms)

root@:26257/defaultdb> select '1.5 year'::interval;
    interval
-----------------
  1 year 6 mons
(1 row)

Time: 0ms total (execution 0ms / network 0ms)

root@:26257/defaultdb> select '1.99 year'::interval;
             interval
-----------------------------------
  1 year 11 mons 26 days 09:36:00
(1 row)

Time: 0ms total (execution 0ms / network 0ms)

root@:26257/defaultdb> select '1.99999 year'::interval;
               interval
--------------------------------------
  1 year 11 mons 29 days 23:54:48.96
(1 row)

Time: 0ms total (execution 0ms / network 0ms)

In the latter two cases, I guess we need to not even store the "extra precision" parts of the interval.

[blathers-crl]

Hi @jordanlewis, please add a C-ategory label to your issue. Check out the label system docs.

_{🦉 Hoot! I am a Blathers, a bot for CockroachDB. My owner is otan.}