Help Center> Cloud Stream Service> SQL Syntax Reference> Built-In Functions> Time Functions
View PDF

Time Functions

Table 1 lists the time functions supported by Stream SQL.

Table 1 Time functions

Function

Returned Data Type

Description

DATE string

DATE

Parses the date string (yyyy-MM-dd) to a SQL date.

TIME string

TIME

Parses the time string (HH:mm:ss) to the SQL time.

TIMESTAMP string

TIMESTAMP

Converts 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.

For 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

Returns the SQL date of UTC time zone.

CURRENT_TIME

TIME

Returns the SQL time of UTC time zone.

CURRENT_TIMESTAMP

TIMESTAMP

Returns the SQL timestamp of UTC time zone.

LOCALTIME

TIME

Returns the SQL time of the current time zone.

LOCALTIMESTAMP

TIMESTAMP

Returns the SQL timestamp of the current time zone.

EXTRACT(timeintervalunit FROM temporal)

INT

Extracts part of the time point or interval. Returns the part in the int type.

For example, extract the date 2006-06-05 and return 5.

FLOOR(timepoint TO timeintervalunit)

TIME

Rounds 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

Rounds 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

Returns the quarter from the SQL date.

(timepoint, temporal) OVERLAPS (timepoint, temporal)

BOOLEAN

Checks 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.

For 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_LOCALTIMESTAMP(long expr)

TIMESTAMP

Converts the time to the local time.

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