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
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, 2016:
- Returns a weeks since value of 40 for a purchase date of 08/02/2016
- Returns a weeks since value of 3 for a purchase date of 22/10/2016
Assuming today is December 5, 2020:
- Returns a weeks since value of 5 for a purchase date of 30/10/2020
- Returns a weeks since value of 14 for a purchase date of 28/08/2016