Help Center/ Data Lake Insight/ SQL Syntax Reference (To Be Offline)/ Historical Versions (Unavailable Soon)/ Flink SQL Syntax/ Built-In Functions/ Temporal Functions
Updated on 2022-09-23 GMT+08:00
View PDF

Temporal Functions

Table 1 lists the time functions supported by Flink SQL.

Function Description

Table 1 Time Function

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