Time Functions

The following time functions are supported. Valid values for <timepart> vary, based on the Composer 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 Composer 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. However, NOW() functionality is not enabled by default. To enable this functionality, you must 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>)