Time-Based Filtering

SAQL gives you many ways to specify the range of dates that you want to look at, such as "all ops from the last fiscal quarter" or "all cases from the last seven days".

Using Date Ranges in Filters

Use these filters to specify the date range you want to look at:

  • Fixed date range, for example between August 1, 2018 and June 2, 2017
  • Relative date range, for example between two years ago and last month
  • Open-ended ranges, for example before 04/2/2018
  • Add and subtract dates, for example all records from three months before yesterday

Example: Display Opportunities Closed This Month

Suppose that you want to see which opportunities closed this month. Your data includes the account name, the close date fields, and the epoch seconds field.

Diagram showing the number of days each account has been opened for.

Use date() to generate the close date in date format. Then use relative date ranges to filter opportunities closed in the current month.

q = load "OpsDates1";
q = filter q by date(’CloseDate_Year’, ‘CloseDate_Month’, ‘CloseDate_Day’) in ["current month" .. "current month"];
q = foreach q generate Account;

If the query is run in May 2018, the resulting data stream contains one entry:

Screenshot displaying opportunities closed in the current month, with seconds epoch time.

To add the close date in a readable format, use toDate().

q = load "OpsDates1";
q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["current month" .. "current month"];
q = foreach q generate Account, toDate('CloseDate_sec_epoch') as 'Close Date';

The resulting data stream includes the full date and time of the close date.

Screenshot displaying opportunities closed in the current month, with seconds epoch.

You can also display just the month and day of the close date.

q = load "OpsDates1";
q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["current month" .. "current month"];
q = foreach q generate Account, 'CloseDate_Month' + "/" + 'CloseDate_Day' as 'Close Date';

The resulting data stream contains the month and day of the close date.

Screenshot displaying opportunities closed in the current month, with close date and day.

Fixed Date Ranges

Use dateRange() to specify a fixed range of dates in a filter:
dateRange(startArray_y_m_d, endArray_y_m_d)

startArray_y_m_d is an array that specifies the start date

endArray_y_m_d is an array that specifies the end date

For example, return all records between October 2, 2014 and August 16, 2016:

q = filter q by date('CreatedDate_Year', 'CreatedDate_Month', 'CreatedDate_Day') in [dateRange([2014,10,2], [2016,8,16])];

Relative Date Ranges

Use relative date ranges to answer questions such as "how many opportunities did each rep close in the past fiscal quarter"? To specify a relative date range, use the in operator on an array with relative date keywords:
in ["relative_date_keyword_1".."relative_date_keyword_2"]

For example, return all records from one year ago up to and including the current year.

q = filter q  by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["1 year ago".."current year"];
Return all records from two quarters ago, up to and including two quarters from now.
q = filter q  by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["2 quarters ago".."2 quarters ahead"];

Return all records from the last two fiscal years, up to and including today.

q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["2 fiscal_years ago".."current day"];

Use these relative date keywords:

  • current day
  • n day(s) ago
  • n day(s) ahead
  • current week
  • n week(s) ago
  • n week(s) ahead
  • current month
  • n month(s) ago
  • n month(s) ahead
  • current quarter
  • n quarter(s) ago
  • n quarter(s) ahead
  • current fiscal_quarter
  • n fiscal_quarter(s) ago
  • n fiscal_quarter(s) ahead
  • current year
  • n year(s) ago
  • n year(s) ahead
  • current fiscal_year
  • n fiscal_year(s) ago
  • n fiscal_year(s) ahead

Only standard fiscal periods are supported. See "About Fiscal Years" in Salesforce Help.

Note

Open-Ended Date Ranges

Use open-ended date ranges for queries such as "List all opportunities closed after 12/23/2014". To specify an open-ended date range, use the in operator on an array with a relative date keyword and a notation for up to or including:

  • in [.."relative_date_keyword"] (up to)
  • in ["relative_date_keyword"..] (up to and including)
For example, return all records up to and including the current month.
q = filter q  by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in [.."1 year ago"];

You can also specify a closed relative date range. For example, return all records from up to and including one year ago.

q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["3 years ago"..];

Add and Subtract Dates

You can add and subtract dates using the relative date keywords. To specify the date range, use the in operator on an array with a relative date keyword, a notation for up to, including, or a range, and the addition or subtraction operators with a time period:

  • in [.."relative_date_keyword +/- time_period"] (up to)
  • in ["relative_date_keyword +/- time_period"..] (up to and including)
  • in ["relative_date_keyword_1".."relative_date_keyword_2 +/- time_period"] (range)
For example, return all records from one year ago, up to and including today.
q = filter q  by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["current day - 1 year"..];
Return all records from today up to two years and three months from now.
q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["current day".."2 years ahead + 3 months"];