Wave Analytics SAQL Reference

SAQL Overview
Enable SAQL Logs in the Browser
SAQL Basic Elements
SAQL Operators
SAQL Statements
SAQL Functions
Aggregate Functions
Date Functions
String Functions
Math Functions
Windowing Functions
coalesce()
PDF

Newer Version Available

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

Date Functions

To specify dates in a SAQL query, use date functions and relative date keywords.

Functions

Relative dates are relative to UTC, not local time. Data returned for relative dates reflect dates based on UTC time, which may be offset from your local time.

Note

This table lists SAQL date functions:
Date Function Description
date(year, month, day) Returns a date. Specify 3 dimensions of a date in the following order: year, month, day. For example: 
1date('OrderDate_Year', 'OrderDate_Month', 'OrderDate_Day')
dateRange(startArray_y_m_d, endArray_y_m_d) Returns a fixed date range. The first parameter is an array that specifies the start date in the range. The second parameter is an array that specifies the end of the range. For example: 
1dateRange([1970, 1, 1], [1970, 1, 31])
daysBetween(date1, date2) Returns the number of days between 2 dates as an integer.
The daysBetween() function can’t take dimensions as arguments directly. Pass toDate() and now(​) functions as arguments. 
1q = foreach q generate daysBetween(​toDate(​OrderDate, “yyyy-MM-dd”​),
2   now()) as daysToShip;
1q = foreach q generate daysBetween(​​toDate(​​OrderDate, “yyyy-MM-dd”​​),
2   toDate(​​ShipDate, “yyyy-MM-dd”​​)) as daysToShip;
1q = foreach q generate daysBetween(​toDate(​OrderDate_​Year + “:” 
2   + OrderDate_​Month + “:” + OrderDate_​Day, “yyyy:MM:dd”​), toDate(​ShipDate_​Year + “:” 
3   + ShipDate_​Month + “:” + ShipDate_Day,​ “yyyy:MM:dd”​)) as daysToShip;
now() Returns current datetime in UTC. This function is valid in a foreach statement only.
1q = foreach q generate now() as now;

This function is commonly used in daysBetween(​) and toString() functions.
toDate(string [,formatString]) Converts a string to a date. If a formatString argument isn’t provided, the function uses the format yyyy-MM-dd HH:mm:ss.
1q = foreach q generate toDate(​OrderDate);
1q = foreach q generate toDate(​OrderDate_​Day + \"-\" +​ OrderDate_​Month + \"-\" + ​OrderDate_​Year, \"dd-MM-yyyy\​");

This function is often passed as an argument to daysBetween(​) or toString().

toDate(epoch_seconds) Converts Unix epoch seconds to a date. If epoch_seconds is 0, toDate(epoch_seconds) returns '1970-01-01 00:00:00'.

This function is convenient for adding or subtracting time periods to or from a date. When adjusting dates for time zone differences, adding or subtracting the number of seconds in the time difference produces the correct local date. If the time crosses the local meridian, a different date is produced.

For example, assuming Current_Date is the current date expressed as the number of seconds since '1970-01-01 00:00:00', then the function toDate(Current_Date - 8*3600) subtracts 8 hours. Refer to Working with Time Zones for a practical example.
toString(date, formatString) Converts a date to a string.
This function must take a toDate() or now(​) function as its first argument. 
1q = foreach q generate toString(now(​), \"yyyy-MM-dd HH:mm:ss\") as ds1;

Specify Fixed Date Ranges

To specify a range for fixed dates, use the dateRange() function. Specify the dates in the order: year, month, day.

Example

1a = filter a by date('year', 'month', 'day') in [dateRange([1970, 1, 1], [1970, 1, 11])];

Specify Relative Date Ranges

To specify a relative date range, use the in operator on an array with relative date keywords. Here are 4 examples:

1a = filter a by date('year', 'month', 'day') in ["1 year ago".."current year"];
2a = filter a by date('year', 'month', 'day') in ["2 quarters ago".."2 quarters ahead"];
3a = filter a by date('year', 'month', 'day') in ["4 months ago".."1 year ahead"];
4a = filter a by date('year', 'month', 'day') in ["2 fiscal_years ago".."current day"];
The relative date keywords are:
  • 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
This table shows the time windows for some of the relative date keywords. In these time window examples, the current day is 2014/12/16 and FiscalMonthOffeset 1 (the fiscal year starts on February 1).
Relative Date Keyword Start Date End Date
current day 2014/12/16 00:00:00 2014/12/16 23:59:59
current quarter 2014/10/1 00:00:00 2014/12/31 23:59:59
1 year ago 2013/1/1 00:00:00 2013/12/31 23:59:59
1 month ahead 2015/1/1 00:00:00 2015/1/31 23:59:59
current fiscal_year 2014/2/1 00:00:00 2015/1/31 23:59:59
current fiscal_quarter 2014/11/1 00:00:00 2015/1/31 23:59:59
2 fiscal_quarters ahead 2015/5/1 00:00:00 2015/7/31 23:59:59
current day - 1 year 2013/12/16 00:00:00 2013/12/16 23:59:59
current fiscal_year + 5 days 2014/2/6 00:00:00 2014/2/6 23:59:59

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

Note

Add and Subtract Dates

You can add and subtract dates using the relative date keywords.

Example

Here are examples of time windows for relative date keywords using addition and subtraction. In these time window examples, the current day is 2014/12/16 and FiscalMonthOffeset 1 (the fiscal year starts on February 1).

In this query, the start date is 2013-12-16 00:00:00 and the end date is open ended: 
1a= filter a by date('year', 'month', 'day') in ["current day - 1 year"..] ;
In this query, the start date is 2014-12-16 00:00:00 and the end date is 2017-3-31 23:59:59:
1a= filter a by date('year', 'month', 'day') in ["current day".."2 years ahead + 3 months"];
Here’s how to determine the end date: the year is 2014, so 2 years ahead is 2016, which has a year end time of 2016-12-31 23:59:59. When you add 3 months, the total end date is 2017-3-31 23:59:59.
In this query, the start date is 2014-2-6 00:00:00 and the end date is 2017-3-31 23:59:59:
1a= filter a by date('year', 'month', 'day') in ["current fiscal_year + 5 days".."2 years ahead + 3 months"];

Use Open-Ended Relative Date Ranges

To build queries like “List all opportunities closed after 12/23/2014” and “Get a list of marketing campaigns from before 04/2/2015,” use open-ended date ranges.

Example

This example shows an open-ended relative date range. 
1a = filter a by date('year','month','day') in [.."current month"];

Example

This example shows an open-ended fixed date range. The date format of OrderDate is yyyy-MM-dd.
1q = filter q by OrderDate in [“2015-01-01”​..];

Working with Time Zones

A practical use of the toDate() function is to calculate time zone changes for a Wave dashboard. This JSON code fragment uses a computeExpression action in a transformation, which in turn uses a saqlExpression to call the toDate() function. This technique enables a dashboard to show the most appropriate time and date, whether local or UTC.

1"Extract_Opportunity": {
2  "action": "computeExpression",
3  "parameters": {
4    "source": "Digest_Opportunity",
5    "mergeWithSource": true,
6    "computedFields": [
7     {
8      "name": "CreatedDateNew",
9      "type": "Date",
10      "format": "MM/dd/yyyy",
11      "saqlExpression": "toDate(CreatedDate_sec_epoch - 8*3600)"
12     }
13    ]
14  }
15},

The example takes an existing date CreatedDate_sec_epoch and subtracts 8 hours to create a new date CreateDateNew. The table shows how the calculation changes the (formatted) CreatedDateNew dates. In each case, the time change has also changed the date.

CreatedDate_sec_epoch CreatedDateNew
2015-11-03T06:49:25.00OZ 11/2/2015
2014-08-19T06:42:33.00OZ 8/18/2014
2014-09-28T03:12:25.00OZ 9/27/2014

Refer to the computeExpression topic for further information.