I am using the Table Model in Apache IoTDB 2.0.5 and need to calculate the number of days between two dates. My approach is to first convert the DATE type to an int64 format (like 20251226), then perform a simple subtraction.

I found that when the two dates are within the same calendar year, the query result is perfectly correct. However, when the dates cross a year boundary, the calculation produces a very large and incorrect value, which does not match the expected result.

Here are my table and query statements:

Calculation within the same year (Correct Result):

  select CAST(CAST('2025-12-26' as DATE) AS int64) as endDate,CAST(CAST('2025-12-25' as DATE) AS int64) as startDate,CAST(CAST('2025-12-26' as DATE) AS int64)-CAST(CAST('2025-12-25' as DATE) AS int64) from ht_boar

Returned Result (Correct):

  +-----------+-------------+--------+
  | endDate| startDate| _col2|
  +-----------+-------------+--------+
  | 20251226| 20251225| 1|
  +-----------+-------------+--------+

Calculation across years (Incorrect Result):

  select CAST(CAST('2026-01-01' as DATE) AS int64) as endDate,CAST(CAST('2025-12-25' as DATE) AS int64) as startDate,CAST(CAST('2026-01-01' as DATE) AS int64)-CAST(CAST('2025-12-25' as DATE) AS int64) from ht_boar

Returned Result (Incorrect. Expected 7, but got 8876):

  +-----------+-------------+--------+
  | endDate| startDate| _col2|
  +-----------+-------------+--------+
  | 20260101| 20251225| 8876|
  +-----------+-------------+--------+

I reviewed the official Apache IoTDB 2.0.5 documentation on date functions and type casting, but did not find explicit documentation for a direct date difference function.

In Apache IoTDB 2.0.5, is there a correct SQL method to directly calculate the actual number of days between two DATE type fields? Is there a fundamental flaw in my current logic? If built-in functions do not support this, does it mean I need to implement this by developing a User-Defined Function (UDF)? If so, are there any implementation guidelines or examples I could refer to?