sql: support more time spans for EXTRACT

[yuzefovich]

Currently we support these "fields": year, quarter, month, week, dayofweek, dayofyear, hour, minute, second, millisecond, microsecond, epoch.

Postgres supports more. Some of the missing ones are century, decade, isodow, isoyear, millenium. The relevant code lives in pkg/sql/sem/builtins/builtins.go.

As a bonus, Postgres returns "double precision" type for EXTRACT, but Cockroach returns "int". It could be fixed as well (cc @jordanlewis in case I'm missing why we do it that way).

[rogaps]

I would like to work on it.

[yuzefovich]

@rogaps great! I'll assign the issue to you.

[rogaps]

I am also going to make EXTRACT returns "float". While I am on it, may I add fractional parts to "second", "millisecond", and "epoch"?

On PostgreSQL:

rogaps=# select extract(second from '2016-02-10 19:46:33.306157519'::timestamp);
 date_part
-----------
 33.306158
(1 row)

rogaps=# select extract(millisecond from '2016-02-10 19:46:33.306157519'::timestamp);
 date_part
-----------
 33306.158
(1 row)

rogaps=# select extract(epoch from '2016-02-10 19:46:33.306157519'::timestamp);
    date_part
------------------
 1455133593.30616
(1 row)

On CockroachDB

root=# select extract(second from '2016-02-10 19:46:33.306157519'::timestamp);
 extract
---------
      33
(1 row)

root=# select extract(millisecond from '2016-02-10 19:46:33.306157519'::timestamp);
 extract
---------
   33306
(1 row)

root=# select extract(epoch from '2016-02-10 19:46:33.306157519'::timestamp);
  extract
------------
 1455133593
(1 row)

I am also going to add "julian" field for EXTRACT to support.

[yuzefovich]

@rogaps thanks for working on this! Now that #41784 has been merged, do you think that this issue can be closed or is there something additional work remaining?

[rogaps]

You are welcome. I am happy to help. I have prepared the bonus (EXTRACT returns float), but I am waiting your approval to add fractional parts to it. If you think it is unnecessary you can close this issue.

[yuzefovich]

I believe it would nice to fix this difference Postgres returns "double precision" type for EXTRACT, but Cockroach returns "int" since it would improve our compatibility. @jordanlewis @mjibson do you guys know why we wouldn't want that?

@rogaps please go ahead with addressing the bonus item.

[maddyblue]

We can change extract to return the same type as postgres, that's fine.