Strange (incorrect?) calculation in minutes in DATEDIFF Function.

[former_SQLA_member326303]

I observe a strange (incorrect?) calculation in minutes (SQL Anywhere Server Version 17.0.10.5963):

BEGIN
declare ClockOn time;
declare ClockOff time;
set ClockOn = cast('09:59:59.999' as time);
set ClockOff = cast('10:00:00.001' as time);
select ClockOn, ClockOff, datediff(hour, ClockOn, ClockOff), 
datediff(minute, ClockOn, ClockOff), datediff(second, ClockOn, ClockOff),
datediff(millisecond, ClockOn, ClockOff);
end;

ClockOn = 09:59:59.999
ClockOff = 10:00:00.001
datediff(millisecond, ClockOn, ClockOff) = 2 : OK
datediff(second, ClockOn, ClockOff) = 0 : OK
datediff(hour, ClockOn, ClockOff) = 0 : OK

datediff(minute, ClockOn, ClockOff) = 1 : Why?

"The DATEDIFF function results are truncated, not rounded, when the result is not an even multiple of the date part." It seems that if we write minute, then first the time is truncated to minutes without rounding (“truncated, not rounded”), and then the difference is calculated. I couldn't find a counterexample.

[johnsmirnios · Accepted solution]

It's not really my area but I do have records of discussions about it from 2015 :). As of that time:

---

SQLA implements DATEDIFF and related functions in a way that is not compatible with IQ / ASE and also in a way that is internally inconsistent. While this appears to be unintended, the behavior has been unchanged since the first implementation (Fall of 1992).

When taking the difference between two datetime values in a given unit (datepart), there are two semantics implemented:

1. Truncation (T). The difference is computed in microseconds, then integer division with truncation is used to find the number of full units of difference.

2. Boundary (B). The number of boundaries of the specified datepart between the two inputs is counted.

Consider two timestamps t1=’2015-12-31 23:59:59.999999’ and ’2016-01-01 00:00:00’. These are only one microsecond apart but they appear on different days / months and years.

Under truncation semantics, DATEDIFF( pp, t1, t2 ) is zero for all date parts except microsecond. The difference (1 microsecond) is computed and divided by the larger unit with integer truncation to zero.

Under boundary semantics, the result for all dateparts would be 1.

            ASE IQ  SQLA    MS1
year        ?   ?   B   B
quarter     ?   ?   B   B
month       B   B   B   B
week        B   B   B   B
day         B   B   B   B
dayofyear   ?   ?   B   B
hour        T   T   T   B
minute      T   T   B   B
second      T   T   T   B
millisecond T   T   B   B
microsecond T   T   B/T B

The SQLA semantics do not match ASE/IQ nor do they match MS. They are internally inconsistent because they switch between interpretations within the hour/minute/second range.

There are related function in SQLA, and these are also not always consistent with the DATEDIFF results:

-          DAYS        - B
-          HOURS       - T
-          MINUTES     - B
-          SECONDS     - B (!)

In HANA there is no support for DATEDIFF. There is a DAYS_BETWEEN (B), SECONDS_BETWEEN (T), and NANO100_BETWEEN(B/T).

In the SQL standard, the issue of boundaries and truncation is largely avoided because arithmetic on datetime values yields an INTERVAL type and the operation is required to have an explicit class of interval (YEAR to MONTH or DAY TO SECOND) and optional precision/scale for the components. Truncation or detection of boundaries is fully under the user’s control.

---

There were discussions about adding extra parameters, backward compatibility issues if we just changed the semantics, etc.

I will leave it as an exercise to the reader to figure out where we ended up...

[former_SQLA_member326303 · Accepted solution]

Thanks for clarifying.

ps If we add the date to the time, then, unfortunately, nothing changes.

BEGIN 
declare ClockOn timestamp;
declare ClockOff timestamp;
set ClockOn = cast('2024-01-15 09:59:59.999' as timestamp);
set ClockOff = cast('2024-01-15 10:00:00.001' as timestamp);
select ClockOn, ClockOff, datediff(hour, ClockOn, ClockOff), 
datediff(minute, ClockOn, ClockOff), datediff(second, ClockOn, ClockOff),
datediff(millisecond, ClockOn, ClockOff);
end;

==>
'2024-01-15 09:59:59.999','2024-01-15 10:00:00.001',0,1,0,2

A little "annoying" is the fact that the difference in seconds is 0, and the difference in minutes is still 1.

[johnsmirnios · Accepted solution]

There was also this note in the old email:

" If truncation semantics are desired, they can be computed using DATEDIFF( microsecond, t1, t2, ) / (integer number of microseconds in datepart)."

So that should be DATEDIFF( microsecond, t1, t2 ) / 60000000 for getting minutes with Truncation semantics.

[VolkerBarth · Accepted solution]

Just for the record, Breck had posted about those details in his blog, too (of course):

• Documenting DATEDIFF
• Beware DATEDIFF Alternatives

[former_SQLA_member326303 · Accepted solution]

Thanks Volker, I somehow missed these posts on Breck's wonderful blog.