Temporal Functions

Updated on 2025-01-22 GMT+08:00

View PDF

Table 1 lists the time functions supported by Flink SQL.

Function Description

**Table 1** Time functions
Function	Return Type	Description
DATE string	DATE	Parse the date string (yyyy-MM-dd) to a SQL date.
TIME string	TIME	Parse the time string (HH:mm:ss) to the SQL time.
TIMESTAMP string	TIMESTAMP	Convert the time string into timestamp. The time string format is yyyy-MM-dd HH:mm:ss.fff.
INTERVAL string range	INTERVAL	There are two types of intervals: yyyy-MM and dd HH:mm:sss.fff'. The range of yyyy-MM can be YEAR or YEAR TO MONTH, with the precision of month. The range of dd HH:mm:sss.fff' can be DAY TO HOUR, DAY TO MINUTE, DAY TO SECOND, or DAY TO MILLISECONDS, with the precision of millisecond. For example, if the range is DAY TO SECOND, the day, hour, minute, and second are all valid and the precision is second. DAY TO MINUTE indicates that the precision is minute. The following is an example: INTERVAL '10 00:00:00.004' DAY TO milliseconds indicates that the interval is 10 days and 4 milliseconds. INTERVAL '10' DAY indicates that the interval is 10 days and INTERVAL '2-10' YEAR TO MONTH indicates that the interval is 2 years and 10 months.
CURRENT_DATE	DATE	Return the SQL date of UTC time zone.
CURRENT_TIME	TIME	Return the SQL time of UTC time zone.
CURRENT_TIMESTAMP	TIMESTAMP	Return the SQL timestamp of UTC time zone.
LOCALTIME	TIME	Return the SQL time of the current time zone.
LOCALTIMESTAMP	TIMESTAMP	Return the SQL timestamp of the current time zone.
EXTRACT(timeintervalunit FROM temporal)	INT	Extract part of the time point or interval. Return the part in the int type. For example, 5 is returned from EXTRACT(DAY FROM DATE "2006-06-05").
FLOOR(timepoint TO timeintervalunit)	TIME	Round a time point down to the given unit. For example, 12:44:00 is returned from FLOOR(TIME '12:44:31' TO MINUTE).
CEIL(timepoint TO timeintervalunit)	TIME	Round a time point up to the given unit. For example, 12:45:00 is returned from CEIL(TIME '12:44:31' TO MINUTE).
QUARTER(date)	INT	Return the quarter from the SQL date.
(timepoint, temporal) OVERLAPS (timepoint, temporal)	BOOLEAN	Check whether two intervals overlap. The time points and time are converted into a time range with a start point and an end point. The function is leftEnd >= rightStart && rightEnd >= leftStart. If leftEnd is greater than or equal to rightStart and rightEnd is greater than or equal to leftStart, true is returned. Otherwise, false is returned. The following is an example: If leftEnd is 3:55:00 (2:55:00+1:00:00), rightStart is 3:30:00, rightEnd is 5:30:00 (3:30:00+2:00:00), and leftStart is 2:55:00, true will be returned. Specifically, true is returned from (TIME '2:55:00', INTERVAL '1' HOUR) OVERLAPS (TIME '3:30:00', INTERVAL '2' HOUR). If leftEnd is 10:00:00, rightStart is 10:15:00, rightEnd is 13:15:00 (10:15:00+3:00:00), and leftStart is 9:00:00, false will be returned. Specifically, false is returned from (TIME '9:00:00', TIME '10:00:00') OVERLAPS (TIME '10:15:00', INTERVAL '3' HOUR).
TO_TIMESTAMP(long expr)	TIMESTAMP	Convert a timestamp to time. The input parameter this function must be of the BIGINT type. Other data types, such as VARCHAR and STRING, are not supported. For example, TO_TIMESTAMP (1628765159000) is converted to 2021-08-12 18:45:59.
UNIX_TIMESTAMP	BIGINT	Returns the timestamp of a specified parameter. The timestamp type is BIGINT and the unit is second. The following methods are supported: UNIX_TIMESTAMP(): returns the timestamp of the current time if no parameter is specified. UNIX_TIMESTAMP(STRING datestr): returns the timestamp indicated by the parameter if only one parameter is contained. The format of datestr must be yyyy-MM-dd HH:mm:ss. UNIX_TIMESTAMP(STRING datestr, STRING format): returns the timestamp indicated by the first parameter if two parameters are contained. The second parameter can specify the format of datestr.
UNIX_TIMESTAMP_MS	BIGINT	Returns the timestamp of a specified parameter. The timestamp type is BIGINT and the unit is millisecond. The following methods are supported: UNIX_TIMESTAMP_MS(): returns the timestamp of the current time if no parameter is specified. UNIX_TIMESTAMP_MS(STRING datestr): returns the timestamp indicated by the parameter if only one parameter is contained. The format of datestr must be yyyy-MM-dd HH:mm:ss.SSS. UNIX_TIMESTAMP_MS(STRING datestr, STRING format): returns the timestamp indicated by the first parameter if two parameters are contained. The second parameter can specify the format of datestr.

Precautions

None

Example

insert into temp SELECT Date '2015-10-11'  FROM  OrderA;//Date is returned
insert into temp1 SELECT Time '12:14:50'  FROM  OrderA;//Time is returned
insert into temp2 SELECT Timestamp '2015-10-11 12:14:50'  FROM  OrderA;//Timestamp is returned

Parent topic: Built-In Functions

Previous topic: String Functions

Next topic: Type Conversion Functions