Cannot subtract infinite timestamps?

lstroudlstroud Registered User

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?

Comments

  • Jim_KnicelyJim_Knicely Administrator, Moderator, Employee, Registered User, VerticaExpert
    edited November 6

    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file