TIMESTAMPDIFF
Calculates the interval between two given dates. The interval timeframe can be changed depending on the fields need.
TIMESTAMPDIFF( <Interval>, <Field or Timestamp 1>, <Field or Timestamp 2>)
- Interval: The timeframe used to measure the time difference between each timestamp/date
- SQL_TSI_FRAC_SECOND: Difference returned in milliseconds
- SQL_TSI_SECOND: Difference returned in seconds
- SQL_TSI_MINUTE: Difference returned in minutes
- SQL_TSI_HOUR: Difference returned in hours
- SQL_TSI_DAY: Difference returned in days
- SQL_TSI_WEEK: Difference returned in weeks
- SQL_TSI_MONTH: Difference returned in months
- SQL_TSI_QUARTER: Difference returned in quarters
- SQL_TSI_YEAR: Difference returned in years
- Field or Timestamp 1: The First Timestamp (Date / Time) to be used in the calculation.
- Field or Timestamp 2: The Second Timestamp (Date / Time) to be used in the calculation.
Note: dates in Audiences need to be formatted as either YYYY-MM-DD or MM/DD/YYYY. Entering a date as 01-02-2023 would be read as Jan-2 not 1-Feb.
Example: create a field which calculates in weeks, how long it has been since an individual's last purchase.
TIMESTAMPDIFF( SQL_TSI_WEEK, [Customer.Last Purchase], CURRENT_TIMESTAMP)
Results:
Assuming today is October 1, 2024:
- Returns a weeks since value of 40 for a purchase date of 02/08/2024 .
- Returns a weeks since value of 3 for a purchase date of 10/22/2024.