Temporal Functions¶
Table 1 lists the time functions supported by Flink SQL.
Function Description¶
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:
|
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_MS | BIGINT | Returns the timestamp of a specified parameter. The timestamp type is BIGINT and the unit is millisecond. The following methods are supported:
|
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