Time-Based Filtering

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.

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:

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.

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.

Fixed Date Ranges

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

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"];

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

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)

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)

q = filter q  by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["current day - 1 year"..];

q = filter q by date('CloseDate_Year', 'CloseDate_Month', 'CloseDate_Day') in ["current day".."2 years ahead + 3 months"];