DATETIME_ADD

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

Syntax 

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.day}
  • 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:

  • MINUTE
  • HOUR
  • DAY
  • WEEK
  • MONTH
  • YEAR

Examples

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

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

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

SELECT COUNT({label/l_loankey}) WHERE {label/l_from.day} >= 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/l_returned.day} >= DATETIME_ADD(MAX({label/l_returned.day}, {dataset/loan}), -7)
DATETIME_DIFF