Time Functions

This applies to: Visual Data Discovery

The following time functions are supported. Valid values for <timepart> vary, based on the Symphony connector selected, but can include YEAR, QUARTER, MONTH, WEEK, WEEK_OF_YEAR, WEEK_OF_MONTH, DAY, DAY_OF_YEAR, DAY_OF_MONTH, DAY_OF_WEEK, HOUR, MINUTE, SECOND, or MILLISECOND. Review the documentation for the Symphony connector for any deviations from this list. Note that the WEEK_OF_YEAR function calculates the week from January 1, not from the week containing January 1.

Function	Description
EXTRACT	Extracts the `<timepart>` of the `<datetime>` field: extract(<timepart>,<datetime>)
NOW	Obtains the current date and time for the derived field. `NOW()` functionality is available when you use a supported connector. Set the `calculations.rle.now.function` property in the `query-engine.properties` file to `true` and restart the query engine microservice. See Query Engine Properties. CASE WHEN [date column] = '' NOW() ELSE [date column] END
TIME_ADD	Adds an interval value to the `<timepart>` of the `<datetime>` field: time_add(<timepart>, <interval>, <datetime>) In the following example, 7 is added to the hour in the field called `date_time_field`: TIME_ADD (hour, +7, date_time_field)
TIME_DIFF	Returns the time difference between two time fields in the unit you request: time_diff('<timepart>', <end_date_field>, <start_date_field>) In the following example, the difference between the values of the ENDDATE and STARTDATE fields is returned in days: time_diff('DAY', ENDDATE, STARTDATE)
TIME_TO_UNIX_TIME	Returns the value of a `<datetime>` field as a Unix time stamp: time_to_unix_time(<datetime>)
TRUNCATE_TIME	Rounds (Truncates) the `<datetime>` field value down to the granularity specified by `<timepart>`: truncate_time(<timepart>,<datetime>)