(v24) Time Functions

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>)