Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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.

asked 15 Jan, 05:01

Ilia63's gravatar image

Ilia63
1.2k525982
accept rate: 44%


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...

permanent link

answered 15 Jan, 09:23

John%20Smirnios's gravatar image

John Smirnios
12.0k396166
accept rate: 37%

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.

(15 Jan, 09:40) Ilia63
Replies hidden
1

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.

(15 Jan, 09:50) John Smirnios
1

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

(16 Jan, 11:14) Volker Barth
Replies hidden
1

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

(17 Jan, 09:54) Ilia63
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×248

question asked: 15 Jan, 05:01

question was seen: 275 times

last updated: 17 Jan, 09:54