The DATETIME_ADD function adds a specified amount of intervals based on the granularity.


SELECT … WHERE DATETIME_ADD(time_attribute, amount) >= time_attribute
SELECT … WHERE DATETIME_ADD(time_attribute, granularity, amount) >= time_attribute

Time attribute can have the following formats:

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

Amount is an integer and specifies the amount of intervals of the given granularity.

Granularity can be one of the following:

  • HOUR
  • DAY
  • WEEK
  • YEAR


Return the number of loans that was returned in the last three days:

SELECT COUNT({label/l_loankey}) WHERE {label/} >= DATETIME_ADD({label/}, -2)

Return the number of loans from the 20th week of 2022:

SELECT COUNT({label/l_loankey}) WHERE {label/} >= DATETIME_ADD("2022-01", WEEK, 19) 

Return the sum of the shipping prices in the 7 days before the last returned loan.

SELECT SUM({fact/l_shippingprice}) WHERE {label/} >= DATETIME_ADD(MAX({label/}, {dataset/loan}), -7)