Cannot subtract infinite timestamps?

lstroud

What does this mean?
[Vertica]VJDBC ERROR: Cannot subtract infinite timestamps

It's being caused by this line:
where datediff(day, "D_TIME_for_AR_by_AR_DOC_Date"."CALENDAR_DT", CURRENT_DATE) BETWEEN 1 AND 364

null values shouldn't break datediff, correct?

Jim_Knicely

It's not NULL values that are giving you grief. The problem is you can not use a date function like DATEDIFF with "infinite" values!

Example:

dbadmin=> CREATE TABLE timestamp_test (c1 INT, c2 TIMESTAMP, c3 TIMESTAMP);
CREATE TABLE

dbadmin=> INSERT INTO timestamp_test SELECT 1, '11/06/2018', '11/08/2018';
 OUTPUT
--------
      1
(1 row)

dbadmin=> INSERT INTO timestamp_test SELECT 2, NULL, '11/08/2018';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT * FROM timestamp_test;
 c1 |         c2          |         c3
----+---------------------+---------------------
  1 | 2018-11-06 00:00:00 | 2018-11-08 00:00:00
  2 |                     | 2018-11-08 00:00:00
(2 rows)

dbadmin=> SELECT *
dbadmin->   FROM timestamp_test
dbadmin->  WHERE DATEDIFF(DAY, c2, c3) BETWEEN 1 AND 365;
 c1 |         c2          |         c3
----+---------------------+---------------------
  1 | 2018-11-06 00:00:00 | 2018-11-08 00:00:00
(1 row)

dbadmin=> INSERT INTO timestamp_test SELECT 3, TIMESTAMP 'INFINITY', '11/08/2018';
 OUTPUT
--------
      1
(1 row)

dbadmin=> SELECT *
dbadmin->   FROM timestamp_test
dbadmin->  WHERE DATEDIFF(DAY, c2, c3) BETWEEN 1 AND 365;
ERROR 2528:  Cannot subtract infinite timestamps

Possible work around:

dbadmin=> SELECT * FROM timestamp_test;
 c1 |         c2          |         c3
----+---------------------+---------------------
  1 | 2018-11-06 00:00:00 | 2018-11-08 00:00:00
  2 |                     | 2018-11-08 00:00:00
  3 | infinity            | 2018-11-08 00:00:00
(3 rows)

dbadmin=> SELECT c1, c2, c3, DATEDIFF(DAY, DECODE(ISFINITE(c2), TRUE, c2, NULL), c3) datediff
dbadmin->   FROM timestamp_test
dbadmin->  WHERE DATEDIFF(DAY, DECODE(ISFINITE(c2), TRUE, c2, c3-1), c3) BETWEEN 1 AND 365;
 c1 |         c2          |         c3          | datediff
----+---------------------+---------------------+----------
  1 | 2018-11-06 00:00:00 | 2018-11-08 00:00:00 |        2
  2 |                     | 2018-11-08 00:00:00 |
  3 | infinity            | 2018-11-08 00:00:00 |
(3 rows)