Cannot subtract infinite timestamps?

Comments

• Options

  Jim_Knicely - Select Field - Administrator

  November 2018 edited November 2018

  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)
  

  0