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