Time Functions
Last updated
Was this helpful?
Last updated
Was this helpful?
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 .
Return Data Type
TIME
Example:
Result: 18:23:04.347264
Result: 14:23:04.347264
EXTRACT(part FROM time)
Description
Return a part of the given time object.
Input
time: a TIME object or column
Return Data Type
INTEGER
Example
Result: 15
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
Return Data Type
TIME
Example:
Result: 07:30:00
Result: 23:30:00
TIME_ADD(time, INTERVAL integer time_part)
Description:
Add a specified time interval to the given time.
Input
integer: the number of time_part
time: a TIME object or column
Return Data Type
TIME
Example:
Result: 15:40:00
TIME_SUB(time, INTERVAL integer time_part)
Description:
Subtract a specified time interval to the given time. T
Input
integer: the number of time_part
time: a TIME object or column
Return Data Type
TIME
Example:
Result: 15:20:00
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
Return Data Type
INTEGER
Example:
Result: 55
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
Return Data Type
TIME
Example:
Result: 15:00:00
FORMAT_TIME(format, time)
Description:
Input
time: a Time object or column
Return Data Type
STRING
Example:
Result: 15:30
PARSE_TIMESTAMP(format, time_expression)
Description:
Convert a string to a time variable according to the specified format.
Input
time_expression: a STRING object or column
Return Data Type
TIME
Example:
Result: 07:30:00
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.
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
part: can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, or HOUR (See more )
timezone: (optional, STRING) It is only available for TIMESTAMP inputs. The default timezone is UTC. You can optionally specify a
time_part: The unit of the time inteval. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR (See more )
time_part: The unit of the time inteval. It can be one of the following: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR (See more )
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 )
time_part: The unit of duration. It can be one of the following MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR (See more )
Transform a time variable into a string variable in a specific format. To see supported format elements, visit .
format: a STRING expression. To see supported format elements, visit .
format: a STRING expression. To see supported format elements, visit .