Analytics SAQL Reference

Overview
Quick Start
Examples
SAQL Reference
SAQL Basic Elements
SAQL Operators
SAQL Statements
SAQL Functions
Aggregate Functions
Date Functions
daysBetween()
date_diff()
now()
date()
toDate()
date_to_epoch()
date_to_string()
toString()
Time-Based Filtering
Day in the Week, Month, Quarter, or Year
Last Day in the Week, Month, Quarter, or Year
Number of Days in the Month, Quarter, or Year
String Functions
Math Functions
Windowing Functions
coalesce
Query Performance
PDF

Newer Version Available

This content describes an older version of this product. View Latest

date_diff()

Returns the amount of time between two dates. This function is only valid in a foreach statement.

Syntax

date_diff(datepart,startdate,enddate)

datepart specifies how you want to measure the time interval:
  • year
  • month
  • quarter
  • day
  • week
  • hour
  • minute
  • second

startdate specifies the start date.

enddate specifies the end date.

Usage

Returns the time difference between two dates in years, months, or days. For example,

date_diff("year", toDate("31-12-2015", "dd-MM-yyyy"), toDate("1-1-2016", "dd-MM-yyyy")) returns 1.

If startdate is after enddate, the difference is returned as a negative number.

You must use date_diff() in a foreach() statement. You cannot use this function in group by, order by, or filter statements.

The maximum amount of time returned is 9,223,372,036,854,775,807 nanoseconds. This maximum amount of time can be measured in any supported datepart value (nanoseconds aren't supported). For example, in days, the maximum amount of time returned is 106,751.99 days (excluding leap seconds).

Example - How Many Weeks Did Each Opportunity Take to Close?

Use date_diff() with datepart = week to calculate how long, in weeks, it took to close each opportunity.

1q = load "DTC_Opportunity";
2q = foreach q generate date_diff("week", toDate(Created_Date_sec_epoch), toDate(Close_Date_sec_epoch) ) as 'Weeks to Close';
3q = order q by 'Weeks to Close';

Example - How Long Ago Was an Opportunity Closed?

Use date_diff() with datepart = month to calculate how many months have passed since each opportunity closed. Use now() as the end date.

1q = load "DTC_Opportunity";
2q = foreach q generate date_diff("month", toDate(Close_Date_sec_epoch), now() ) as 'Months Since Close';
3q = order q by 'Months Since Close';