Updated on 2025-09-05 GMT+08:00

Temporal Functions

The following table lists the temporal functions supported by SecMaster SQL syntax.

Table 1 Temporal functions supported by SecMaster SQL syntax

Function

Function Description

LOCALTIME

Returns the current SQL time in the local time zone, with the return type of TIME(0). In streaming mode, it is evaluated for each record. In batch processing mode, it is evaluated once at the beginning of the query and the same result is used for each row.

LOCALTIMESTAMP

Returns the current SQL timestamp in the local time zone, with the return type of TIMESTAMP(3). In streaming mode, it is evaluated for each record. In batch processing mode, it is evaluated once at the beginning of the query and the same result is used for each row.

CURRENT_TIME

Returns the current SQL time in the local time zone, which is a synonym for LOCALTIME.

CURRENT_DATE

Returns the current SQL date in the local time zone. In streaming mode, it is evaluated for each record. In batch processing mode, it is evaluated once at the beginning of the query and the same result is used for each row.

CURRENT_TIMESTAMP

Returns the current SQL timestamp in the local time zone, with the return type of TIMESTAMP_LTZ(3). In streaming mode, it is evaluated for each record. In batch processing mode, it is evaluated once at the beginning of the query and the same result is used for each row.

NOW()

Returns the current SQL timestamp in the local time zone, which is a synonym for CURRENT_TIMESTAMP.

YEAR(date)

Returns the year from SQL date. It is equivalent to EXTRACT(YEAR FROM date). For example, YEAR(DATE '1994-09-27') returns 1994.

MONTH(date)

Returns the month of the year for a specific SQL date. The value is an integer ranging from 1 to 12. It is equivalent to EXTRACT(MONTH FROM date). For example, MONTH(DATE '1994-09-27') returns 9.

WEEK(date)

Returns the week of the year for a specific SQL date. The value is an integer ranging from 1 to 53. It is equivalent to EXTRACT(WEEK FROM date). For example, WEEK(DATE '1994-09-27') returns 39.

DAYOFYEAR(date)

Returns the day of the year for a specific SQL date. The value is an integer ranging from 1 to 366. It is equivalent to EXTRACT(DOY FROM date). For example, DAYOFYEAR(DATE '1994-09-27') returns 270.

DAYOFMONTH(date)

Returns the day of the month for a specific SQL date. The value is an integer ranging from 1 to 31. It is equivalent to EXTRACT(DAY FROM date). For example, DAYOFMONTH(DATE '1994-09-27') returns 27.

DAYOFWEEK(date)

Returns the day of the week for a specific SQL date. The value is an integer ranging from 1 to 7. It is equivalent to EXTRACT(DOW FROM date). For example, DAYOFWEEK(DATE '1994-09-27') returns 3.

HOUR(timestamp)

Returns the hour of the day from a specific SQL timestamp. The value is an integer ranging from 0 to 23. It is equivalent to EXTRACT(HOUR FROM timestamp). For example, HOUR(TIMESTAMP '1994-09-27 13:14:15') returns 13.

MINUTE(timestamp)

Returns the minute of the hour from a specific SQL timestamp. The value is an integer ranging from 0 to 59. It is equivalent to EXTRACT(MINUTE FROM timestamp). For example, MINUTE(TIMESTAMP '1994-09-27 13:14:15') returns 14.

DATE_FORMAT(timestamp, string)

Converts the timestamp to a string in the specified date format. The format string is compatible with Java's SimpleDateFormat.

TIMESTAMPADD(timeintervalunit, interval, timepoint)

Adds an interval to a time point or subtracts an interval from a time point. The unit of the time is determined by timeintervalunit. For example, TIMESTAMPADD(DAY,-3,NOW()) subtracts three days from the current time and returns the same time of the date three days ago.