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"

String formats

The strings must be in the following formats:

"YYYY-MM-DD HH24:MI" for minutes, "YYYY-MM-DD HH24" for hours, "YYYY-MM-DD" for days, "YYYY-MM" for months, "YYYY-WW" for weeks, and "YYYY" for years.

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

Note

If the time attribute is a string, you must specify the granularity.
The granularity of the time attribute and the specified granularity in the function may be different.
If you do not specify the granularity, the granularity of the time attribute is used.

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