sql: INTERVAL output doesn't match PG

[awoods187]

In pg (http://www.postgresqltutorial.com/postgresql-interval/):

SELECT
 now(),
 now() - INTERVAL '1 year 3 hours 20 minutes' 
             AS "3 hours 20 minutes ago of last year";

In master (v19.1.0-beta.20190304-458-g70e3468),

root@localhost:26257/tpcc> SELECT                                                                                                                                                                                                                                         now(),                                                                                                                                                                                                                                                                   now() - INTERVAL '1 year 3 hours 20 minutes'                                                                                                                                                                                                                                         AS "3 hours 20 minutes ago of last year";
                now                | 3 hours 20 minutes ago of last year
+----------------------------------+-------------------------------------+
  2019-03-15 21:53:18.633297+00:00 | 2018-03-15 18:33:18.633297+00:00
(1 row)

Time: 15.117597ms

knz edit: this should be rejected an unimplemented error. The fact we accept this silently and do the wrong thing is a bug. Please file separately. Thanks. The reference example you used is outdated. Run this in a new postgres to see the behavior is the same.

Update
Filed #35874

[awoods187]

In pg:

SELECT
    EXTRACT (
        MINUTE
        FROM
            INTERVAL '5 hours 21 minutes'
    );
21

Returns
In crdb:

root@localhost:26257/tpcc> SELECT                                                                                                                                                                                                                                            EXTRACT (                                                                                                                                                                                                                                                                    MINUTE                                                                                                                                                                                                                                                                   FROM                                                                                                                                                                                                                                                                         INTERVAL '5 hours 21 minutes'                                                                                                                                                                                                                                    );
pq: unknown signature: extract(string, interval)

knz edit: I had started a PR for this #27502 but it was never complete. Don't remember why. We also don't have telemetry for this.
Update
This is non-urgent but we should probably finish that PR/add telemetry.

[awoods187]

None of the interval styles work in CRDB:

root@localhost:26257/tpcc> SET intervalstyle = 'sql_standard';
pq: invalid value for parameter "IntervalStyle": "sql_standard"
DETAIL: this parameter is currently recognized only for compatibility and has no effect in CockroachDB.
HINT: Available values: postgres

root@localhost:26257/tpcc> SET intervalstyle = 'postgres';
SET

Time: 6.701231ms

root@localhost:26257/tpcc> SET intervalstyle = 'postgres_verbose';
pq: invalid value for parameter "IntervalStyle": "postgres_verbose"
DETAIL: this parameter is currently recognized only for compatibility and has no effect in CockroachDB.
HINT: Available values: postgres

root@localhost:26257/tpcc> SET intervalstyle = 'iso_8601';
pq: invalid value for parameter "IntervalStyle": "iso_8601"
DETAIL: this parameter is currently recognized only for compatibility and has no effect in CockroachDB.
HINT: Available values: postgres

knz edit these are simply unimplemented features. Never was on any roadmap. We don't have telemetry for them. Should we add telemetry?
Update
Let's add telemetry but it's non-urgent

[awoods187]

Interval can't go as far back on CRDB as it can on PG:

INSERT
INTO
    intervals
VALUES
(17, INTERVAL'-178000000y');
pq: overflow during Encode

INSERT
INTO
    intervals
VALUES
(17, INTERVAL'-17800000y');
pq: overflow during Encode

INSERT
INTO
    intervals
VALUES
(17, INTERVAL'-1780000y');
pq: overflow during Encode

INSERT
INTO
    intervals
VALUES
(17, INTERVAL'-178000y');
pq: overflow during Encode

INSERT
INTO
    intervals
VALUES
(17, INTERVAL'-17800y');                                                                                                                                                                                                                                                                       
pq: overflow during Encode

INSERT
INTO
    intervals
VALUES
(17, INTERVAL'-1780y');
INSERT 1
Time: 840.276319ms

root@localhost:26257/tpcc> select * from intervals;
  a  |                 b
+----+-----------------------------------+
  17 | -1780 years

The same is true for positive values.

knz edit: BC dates are already tracked in #28099. The large values in magnitude are "just" a design difference which we should document as known limitation. Maybe we should also track in telemetry, WDYT?
Update
I definitely think we should track this as it is different than PG and perhaps may cause a problem. @rmloveland we should not this somewhere as well too

[awoods187]

Nanoseconds work in CRDB but not in PG (https://www.postgresql.org/docs/current/datatype-datetime.html)

SELECT INTERVAL '6 years 5 months 4 days 3 hours 2 minutes 1 second 1 microsecond 1 nanosecond';
                ?column?
+---------------------------------------+
  6 years 5 mons 4 days 03:02:01.000001
(1 row)

Time: 2.014446ms

knz edit: pg does not support (and rejects) the 'nanoseconds' specifier. Now that crdb does not support nanoseconds any more, the specifier should also be rejected during parsing. Please file a separate issue.
Update
Filed #35872 for this comment

[knz]

@jordanlewis I think most of the issues here have been addressed, it may be possible to close this issue.

[jordanlewis]

@mjibson could you please check out whether this issue is closeable? I suspect not, given what you've learned recently about intervals. If it's not closeable, could you please write down a short summary in this issue about your learnings? Thanks!

[maddyblue]

We still don't have:

• intervalstyle
• extract(minute from interval)
• the same range of possible values

[aashipov]

SELECT (DATE_RANGE.END - DATE_RANGE.BEGIN) AS DATE_DIFFERENCE, EXTRACT(DAYS FROM (DATE_RANGE.END - DATE_RANGE.BEGIN)) AS DAYS
FROM
(SELECT date_trunc('microsecond', timestamp '2020-06-22 07:50:18.567579') AS END,
date_trunc('microsecond', timestamp '2019-09-01 07:50:18.552490') AS BEGIN)
AS DATE_RANGE;

Postgresql: '0 years 0 mons 295 days 0 hours 0 mins 0.015089 secs', 295
CockroachDB: '0 years 0 mons 0 days 7080 hours 0 mins 0.015089 secs', 0

[knz]

cc @otan you might find this interesting

[rafiss]

relates a bit to #59720 which is about parsing intervals.

[otan]

we're only missing intervalstyle now i think.

[rafiss]

Closing this meta-issue in favor of a specific one for IntervalStyle: #61167