Time Functions

CURRENT_TIME

CURRENT_TIME([timezone])

Description:

Return the current time as a time variable.

Input

• timezone(optional, STRING): The default timezone is UTC. You can optionally specify a time zone.

Return Data Type

TIME

Example:

CURRENT_TIME()

Result: 18:23:04.347264

CURRENT_TIME("merica/New_York")

Result: 14:23:04.347264

EXTRACT

EXTRACT(part FROM time)

Description

Return a part of the given time object.

Input

• part: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, or HOUR (See more details)

• time: a TIME object or column

Return Data Type

INTEGER

Example

EXTRACT(HOUR FROM TIME "15:30:00")

Result: 15

TIME

TIME(time_expression,[timezone])

Description:

Convert a string, timestamp, or datetime expression to a time variable in a "hh-mm-ss" format.

Input

• time_expression: a STRING, TIMESTAMP, or DATETIME object or column

• timezone: (optional, STRING) It is only available for TIMESTAMP inputs. The default timezone is UTC. You can optionally specify a time zone.

Return Data Type

TIME

Example:

TIME(TIMESTAMP("2020-12-25 15:30:00+08"))

Result: 07:30:00

TIME(TIMESTAMP("2020-12-25 15:30:00+08"), "America/Los_Angeles")

Result: 23:30:00

TIME_ADD

TIME_ADD(time, INTERVAL integer time_part)

Description:

Add a specified time interval to the given time.

Input

• time_part: The unit of the time inteval. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR (See more details)

• integer: the number of time_part

• time: a TIME object or column

Return Data Type

TIME

Example:

TIME_ADD(TIME("15:30:00"), INTERVAL 10 MINUTE)

Result: 15:40:00

TIME_SUB

TIME_SUB(time, INTERVAL integer time_part)

Description:

Subtract a specified time interval to the given time. T

Input

• time_part: The unit of the time inteval. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR (See more details)

• integer: the number of time_part

• time: a TIME object or column

Return Data Type

TIME

Example:

TIME_SUB(TIME("15:30:00"), INTERVAL 10 MINUTE)

Result: 15:20:00

TIME_DIFF

TIME_DIFF(time1, time2, time_part)

Description:

Return the difference between two times(time1-time2). The difference is expressed in the unit of time_part.

Input

• time1: a TIME object or column

• time2: a TIME object or column

• time_part: The unit of the difference between two times. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, or HOUR (See more details)

Return Data Type

INTEGER

Example:

TIME_DIFF(TIME("15:30:00"), TIME("14:35:00"), MINUTE)

Result: 55

TIME_TRUNC

TIME_TRUNC(time, time_part)

Description:

Truncate a specific time to the start of a given unit of duration and returns a new time. The unit of duration is defined by time_part.

Input

• time: a TIME object or column

• time_part: The unit of duration. It can be one of the following MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR (See more details)

Return Data Type

TIME

Example:

TIME_TRUNC(TIME("15:30:00"), HOUR)

Result: 15:00:00

FORMAT_TIME

FORMAT_TIME(format, time)

Description:

Transform a time variable into a string variable in a specific format. To see supported format elements, visit here.

Input

• format: a STRING expression. To see supported format elements, visit here.

• time: a Time object or column

Return Data Type

STRING

Example:

FORMAT_TIME("%R", TIME "15:30:00")

Result: 15:30

PARSE_TIME

PARSE_TIMESTAMP(format, time_expression)

Description:

Convert a string to a time variable according to the specified format.

Input

• format: a STRING expression. To see supported format elements, visit here.

• time_expression: a STRING object or column

Return Data Type

TIME

Example:

PARSE_TIME("%I:%M:%S", "07:30:00")

Result: 07:30:00

Details about date_part

Part	Description
MICROSECOND	The microsecond of the time.
MILLISECOND	The millisecond of the time.
SECOND	The second of the time.
MINUTE	The minute of the time.
HOUR	The hour of the time.

Supported Format Elements

Format element	Description	Example
%H	Return the hour in the 24-hour clock (00-23).	15
%I	Return the hour in the 12-hour clock (01-12).	03
%k	Return the hour (0-23) in the 24-hour clock without leading 0.	15
%l	Return the hour (1-12) in the 12-hour clock without leading 0	3
%M	Return the minute of the time (00-59).	31
%n	Return a newline character.
%P	Return a lowercase "am" or "pm".	pm
%p	Return an uppercase "AM" or "PM".	PM
%R	Return the time in a format of "%H:%M".	15:31
%r	Return the time in the 12-hour clock with AM/PM notation.	03:31:13 PM
%S	Return the second of the time (00-60).	13
%T	Return the time in a format of %H:%M:%S.	15:31:13
%t	Return a tab character.
%X	Return the time in a format of "HH:MM:SS"	15:31:13
%%	Return a single "%" character.	%
%E#S	Return the seconds with the given number of digits of fractional precision. The "#" should be replaced by a number.	"%E3S" returns 37.021
%E*S	Return the seconds with full fractional precision.	37.021851