ProductsPricingGlossary Launch Acho
Search
⌃K
Links

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
Previous
Datetime Functions
Next
Timestamp Functions
Last modified 2yr ago