Time Functions
CURRENT_TIME([timezone])
Description:
Return the current time as a time variable.
Input
Return Data Type
TIME
Example:
CURRENT_TIME()
Result: 18:23:04.347264
CURRENT_TIME("merica/New_York")
Result: 14:23:04.347264
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
EXTRACT(HOUR FROM TIME "15:30:00")
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
: 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:
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, 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:
TIME_ADD(TIME("15:30:00"), INTERVAL 10 MINUTE)
Result: 15:40:00
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:
TIME_SUB(TIME("15:30:00"), INTERVAL 10 MINUTE)
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
: 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:
TIME_DIFF(TIME("15:30:00"), TIME("14:35:00"), MINUTE)
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
: 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:
TIME_TRUNC(TIME("15:30:00"), HOUR)
Result: 15:00:00
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
time
: aTime
object or column
Return Data Type
STRING
Example:
FORMAT_TIME("%R", TIME "15:30:00")
Result: 15:30
PARSE_TIMESTAMP(format, time_expression)
Description:
Convert a string to a time variable according to the specified format.
Input
time_expression
: aSTRING
object or column
Return Data Type
TIME
Example:
PARSE_TIME("%I:%M:%S", "07:30:00")
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 |
Last modified 2yr ago