Getting a BIGINT from DATEDIFF

Did you know that DATEDIFF returns a signed INTEGER value? Not BIGINT?

SELECT EXPRTYPE ( 'SELECT DATEDIFF ( DAY, CURRENT TIMESTAMP, CURRENT TIMESTAMP + 1 )', 1 ) AS "Data Type";

Data Type
'int'

Who cares, you ask?

Well, did you know that DATEDIFF ( MILLISECOND, ... ) craps out at 25 days?

More specifically, at some point between 24 and 25 for the number of days returned by DATEDIFF ( DAY, ... ), the same call using DATEDIFF ( MILLISECOND, ... ) will blow past the limit for INTEGER.

BEGIN
   DECLARE @sqlcode      INTEGER;
   DECLARE @sqlstate     VARCHAR ( 5 );
   DECLARE @errormsg     VARCHAR ( 32767 );

   DECLARE @ok           BIGINT;
   DECLARE @splat        BIGINT;

   SELECT DATEDIFF ( MILLISECOND,
                     CURRENT TIMESTAMP,
                     DATEADD ( DAY, 24, CURRENT TIMESTAMP ) )
     INTO @ok;

   BEGIN

      SELECT DATEDIFF ( MILLISECOND,
                        CURRENT TIMESTAMP,
                        DATEADD ( DAY, 25, CURRENT TIMESTAMP ) )
        INTO @splat;

      EXCEPTION WHEN OTHERS THEN
         SELECT SQLCODE, SQLSTATE, ERRORMSG() 
           INTO @sqlcode, @sqlstate, @errormsg;
         MESSAGE STRING ( 
            'EXCEPTION raised by "SELECT INTO @splat" at ', 
            CURRENT TIMESTAMP, 
            ': SQLCODE = ', @sqlcode,  
            ', SQLSTATE = ', @sqlstate,  
            ', ERRORMSG() = ', @errormsg ) 
            TO CLIENT;

   END;

   SELECT @ok, @splat;

END;

@ok,@splat
2073600000,(NULL)

EXCEPTION raised by "SELECT INTO @splat" at 2010-10-16 08:52:49.610: SQLCODE = -158, SQLSTATE = 22003, ERRORMSG() = Value datediff(millisecond,2010-10-16 08:52:49.610,2010-11-10 08:52:49.610) out of range for destination

You can probably figure out the exact "splat!" point between 24 and 25 days using DATEADD ( HOUR, ... ) or MINUTE or even SECOND. Note, however, that DATEDIFF ( SECOND, ... ) and MINUTE have their own splat! points, and the effect of multiple splat! points on code verbosity will soon become apparent.

Who cares? I do!

Or at least, the Foxhound Database Monitor cares... Foxhound lives and dies on the calculation of elapsed times between two arbitrary timestamps. Tiny values need to be reasonably precise, so the calculations are done in milliseconds. Huge intervals (days, weeks, years) must to be accomodated so BIGINT is used. And to reduce code complexity BIGINT milliseconds are used throughout (there's a bunch of code devoted to formatting intervals for display, and sticking to milliseconds for input to that code makes it easier).

Sounds like a job for FLOAT? Hah! ...don't talk to me about floating point numbers, they're icky sloppy things, not to be touched or handled without gloves.

Yes, I am a Data Type Bigot and proud of it. In olden days my motto was, "If it ain't greater than zero and less than 32767 I'm not interested!"

Now it's "Give me fixed point or give me death!"

Besides, DATEDIFF returns an INTEGER, and it *still* craps out at 25 days if you CAST it as FLOAT.

One workaround is to accept FLOAT-like behavior in a BIGINT value (large values are not perfectly precise), and turn failing DATEDIFF ( MILLISECOND, ... ) calls into ones that work: DATEDIFF ( SECOND, ... ) * 1000, DATEDIFF ( MINUTE, ... ) * 60 * 1000 and so on.

Here's a warts-and-all excerpt from Foxhound; the columns to look at are started_at, completed_at and run_msec:

CREATE TABLE rroad_purge_run (
   run_number     BIGINT NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY, -- do not INSERT or UPDATE this column
   progress       VARCHAR ( 100 ) NOT NULL DEFAULT 'Starting',
   started_at     TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,      -- do not INSERT or UPDATE this column
   is_complete    VARCHAR ( 1 ) NOT NULL DEFAULT 'N' CHECK ( @complete IN ( 'Y', 'N' ) ),
   completed_at   TIMESTAMP NOT NULL DEFAULT TIMESTAMP,              -- do not INSERT or UPDATE this column

   must_trigger_next_purge                    VARCHAR ( 1 ) NOT NULL DEFAULT 'N' CHECK ( must_trigger_next_purge IN ( 'Y', 'N' ) ),
   sample_purge_interval                      VARCHAR ( 100 ) NOT NULL DEFAULT '',  
   uninteresting_connections_purge_interval   VARCHAR ( 100 ) NOT NULL DEFAULT '',  
   purge_speed                                VARCHAR ( 100 ) NOT NULL DEFAULT '',   
   exception_delete_count                     BIGINT NOT NULL DEFAULT 0,
   orphan_sample_set_delete_count             BIGINT NOT NULL DEFAULT 0,
   old_sample_set_delete_count                BIGINT NOT NULL DEFAULT 0,
   uninteresting_connections_delete_count     BIGINT NOT NULL DEFAULT 0,
   exception_delete_msec                      INTEGER NOT NULL DEFAULT 0,
   orphan_sample_set_delete_msec              INTEGER NOT NULL DEFAULT 0,
   old_sample_set_delete_msec                 INTEGER NOT NULL DEFAULT 0,
   uninteresting_connections_delete_msec      INTEGER NOT NULL DEFAULT 0,

   run_msec       BIGINT NOT NULL COMPUTE (
      CASE 
         WHEN ABS ( DATEDIFF ( YEAR, started_at, completed_at ) ) >= 4083
            THEN CAST ( DATEDIFF ( HOUR, started_at, completed_at ) AS BIGINT ) * 60 * 60 * 1000

         WHEN ABS ( DATEDIFF ( YEAR, started_at, completed_at ) ) >= 68
            THEN CAST ( DATEDIFF ( MINUTE, started_at, completed_at ) AS BIGINT ) * 60 * 1000

         WHEN ABS ( DATEDIFF ( DAY, started_at, completed_at ) ) >= 24
            THEN CAST ( DATEDIFF ( SECOND, started_at, completed_at ) AS BIGINT ) * 1000

         ELSE DATEDIFF ( MILLISECOND, started_at, completed_at )
      END ) );

The table rroad_purge_run is used for monitoring Foxhound itself, in particular the internal database purge process.

The started_at column is initialized by DEFAULT CURRENT TIMESTAMP when the row is inserted, and completed_at is updated by the DEFAULT TIMESTAMP clause every time the row is updated. The completed_at column doesn't contain a real "completed at" value until the purge process is finished... unless the process crashes along the way, in which case it's pretty close.

The run_msec COMPUTE clause shows how various DATEDIFF splat! points are avoided by making some conservative tests: If the difference is 4083 years or more use DATEDIFF ( HOUR, ... ), else if the difference is 68 years or more use MINUTE, else so on.

Here is a simulation showing how the COMPUTE works for small intervals. After the first UPDATE run_msec is 593 and after the second run_msec = 1140:

INSERT rroad_purge_run ( run_number ) VALUES ( DEFAULT ); -- work begins

WAITFOR DELAY '00:00:00.5'; -- some work is done

UPDATE rroad_purge_run
   SET exception_delete_count = 123 -- a column is changed
 WHERE run_number = 1;

SELECT started_at, completed_at, run_msec 
  FROM rroad_purge_run;

started_at,completed_at,run_msec
'2010-10-16 08:11:37.910','2010-10-16 08:11:38.503',593

WAITFOR DELAY '00:00:00.5'; -- more work is done

UPDATE rroad_purge_run
   SET orphan_sample_set_delete_count = 567 -- another column is changed
 WHERE run_number = 1;

SELECT started_at, completed_at, run_msec 
  FROM rroad_purge_run;

started_at,completed_at,run_msec
'2010-10-16 08:11:37.910','2010-10-16 08:11:39.050',1140

Here's another simulation showing how the COMPUTE works for 23 versus 25 days; there's no EXCEPTION, but there is also a slight loss of precision (the 11 milliseconds is lost: 1987200011 versus 2160000000):

UPDATE rroad_purge_run 
   SET completed_at = DATEADD ( DAY, 23, DATEADD ( MILLISECOND, 11, started_at ) ) 
 WHERE run_number = 1;

SELECT started_at, completed_at, run_msec 
  FROM rroad_purge_run;

started_at,completed_at,run_msec
'2010-10-16 08:11:37.910','2010-11-08 08:11:37.921',1987200011

UPDATE rroad_purge_run 
   SET completed_at = DATEADD ( DAY, 25, DATEADD ( MILLISECOND, 11, started_at ) ) 
 WHERE run_number = 1;

SELECT started_at, completed_at, run_msec 
  FROM rroad_purge_run;

started_at,completed_at,run_msec
'2010-10-16 08:11:37.910','2010-11-10 08:11:37.921',2160000000