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:
Result: 18:23:04.347264
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
: aTIME
object or column
Return Data Type
INTEGER
Example
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
: aSTRING
,TIMESTAMP
, orDATETIME
object or columntimezone
: (optional,STRING
) It is only available forTIMESTAMP
inputs. The default timezone is UTC. You can optionally specify a time zone.
Return Data Type
TIME
Example:
Result: 07:30:00
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 oftime_part
time
: aTIME
object or column
Return Data Type
TIME
Example:
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 oftime_part
time
: aTIME
object or column
Return Data Type
TIME
Example:
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
: aTIME
object or columntime2
: aTIME
object or columntime_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:
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
: aTIME
object or columntime_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:
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
: aSTRING
expression. To see supported format elements, visit here.time
: aTime
object or column
Return Data Type
STRING
Example:
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
: aSTRING
expression. To see supported format elements, visit here.time_expression
: aSTRING
object or column
Return Data Type
TIME
Example:
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
Last updated