Wednesday, October 27, 2010

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

1 comment:

Anonymous said...

Breck, note the behaviour change for SA 12:

"In previous releases, the DATEDIFF function returned an INTEGER for date parts of hours and smaller. DATEDIFF now returns an a BIGINT for these date parts." - That could shorten your code:)

FWIW, in SA 12, DATEDIFF supports the new microsecond part - if you like Foxhound to get even more accurate...

Regards
Volker