The DATETIME_DIFF function calculates the difference between two timestamps or dates. The resulting number is the difference between the two specified timestamps/dates based on the granularity.

The function can be used, for example, to calculate the duration of your business processes.


SELECT DATETIME_DIFF(time_attribute1, time_attribute2)
SELECT DATETIME_DIFF(time_attribute1, time_attribute2, granularity)
SELECT … WHERE DATETIME_DIFF(time_attribute1, time_attribute2) > 5

Time attributes can have the following formats:

  • Timestamp: {label/}
  • Date and time macro: THIS(DAY)
  • Strings: "2022"

Granularity is optional and can be one of the following:

  • HOUR
  • DAY
  • WEEK
  • YEAR


To count the difference between two dates without specifying the granularity (the granularity is taken from the attributes):

SELECT DATETIME_DIFF({label/}, {label/})

Difference in days between two time attributes with hour granularity:

SELECT DATETIME_DIFF({label/l_from.hour}, {label/l_to.hour}, DAY)

Filter data where the duration is greater than the specified amount:

SELECT COUNT({dataset/item}) WHERE DATETIME_DIFF({label/}, THIS(DAY)) > 10