CURRENT_TIME
CURRENT_TIME([timezone]) Returns current time in the timezone time zone (default UTC). Return value is of time type.
SELECT CURRENT_TIME()
SELECT CURRENT_TIME('America/Los_Angeles')
SELECT TYPEOF(CURRENT_TIME('America/Los_Angeles'))
SELECT CAST(CURRENT_TIME('America/Los_Angeles') AS string)
SELECT FORMAT_TIME('%H:%M', CURRENT_TIME())
Here are some examples of comparison and interval arithmetic operations on the time type:
SELECT CURRENT_TIME() > PARSE_TIME('%H:%M', '10:10')
SELECT CURRENT_TIME() > CURRENT_TIME() - INTERVAL 1 SECOND
SELECT
CURRENT_TIME() AS now,
(CURRENT_TIME() - INTERVAL 1 HOUR) AS one_hour_ago
TIME constructor
TIME(hour, min, sec[, microsecond])
Constructs value of type time based on hour, min, sec, and, optionally, microsecond.
SELECT TIME(10,30,10)
SELECT TYPEOF(TIME(10,30,10))
Here are some examples of comparison and interval arithmetic operations on the time type:
SELECT TIME(10,30,10) > PARSE_TIME('%H.%M.%S', '10.30.10')
SELECT TIME(10,30,10) > PARSE_TIME('%H.%M.%S', '10.30.10') - INTERVAL 1 SECOND
SELECT
TIME(10,31,10) AS now,
(TIME(10,31,10) - INTERVAL 1 MINUTE) AS one_minute_ago
TIME(datetime)
Returns the time part of datetime.
SELECT CURRENT_DATETIME()
SELECT TIME(CURRENT_DATETIME())
SELECT TYPEOF(TIME(CURRENT_DATETIME()))
Here are some examples of comparison and interval arithmetic operations on the time type:
SELECT TIME(CURRENT_DATETIME()) > TIME(11,10,10)
SELECT TIME(CURRENT_DATETIME()) > TIME(CURRENT_DATETIME()) - INTERVAL 1 SECOND
SELECT
TIME(CURRENT_DATETIME()) AS now,
(TIME(CURRENT_DATETIME()) - INTERVAL 1 MINUTE) AS one_minute_ago
CURRENT_TIME([timezone]) Returns current time in the timezone time zone (default UTC). Return value is of time type.
SELECT CURRENT_TIMESTAMP()
SELECT TIME(CURRENT_TIMESTAMP())
SELECT TYPEOF(TIME(CURRENT_TIMESTAMP()))
Here are some examples of comparison and interval arithmetic operations on the time type:
SELECT TIME(CURRENT_TIMESTAMP()) > TIME(11,10,10)
SELECT TIME(CURRENT_TIMESTAMP()) > TIME(CURRENT_TIMESTAMP()) - INTERVAL 1 SECOND
SELECT
TIME(CURRENT_TIMESTAMP()) AS now,
(TIME(CURRENT_TIMESTAMP()) - INTERVAL 1 MINUTE) AS one_minute_ago
FORMAT_TIME
FORMAT_TIME(format, time) Converts time to string using the given format.
The format specification is the standard strftime with CCTZ extensions.
SELECT FORMAT_TIME('%H:%M:%E*S', TIME '21:30:20.345')
SELECT FORMAT_TIME('%I:%M:%S', TIME '21:30:20.345')
FORMAT_ISO8601 time
FORMAT_ISO8601(time)
Converts a time value to string using the ISO 8601 extended format. Returns string formatted using HH:MM:SS[.cccccc] as the format specifier for time. It formats it as a 24 hour time, with optional fractional seconds.
SELECT FORMAT_ISO8601(TIME '11:11:10.1002')
SELECT FORMAT_ISO8601(TIME '21:11:10')
PARSE_TIME
PARSE_TIME(format, string) Parses the date string (formatted using the given format) into a time value.
The format specification is the standard strptime with CCTZ extensions.
SELECT PARSE_TIME('%H:%M:%S', '10:30:10')
SELECT TYPEOF(PARSE_TIME('%H:%M:%S', '10:30:10'))
Here are some examples of comparison and interval arithmetic operations on the time type:
SELECT TIME '10:31:00' > PARSE_TIME('%H.%M.%S', '10.30.10')
SELECT TIME(10,30,10) > PARSE_TIME('%H.%M.%S', '10.30.10') - INTERVAL 1 SECOND
SELECT
PARSE_TIME('%H:%M:%S', '10:31:10') AS now,
(PARSE_TIME('%H:%M:%S', '10:31:10') - INTERVAL 1 MINUTE) AS one_minute_ago
PARSE_TIME_ISO8601
PARSE_TIME_ISO8601(string) Parses a time from an ISO 8601 string without a timezone.
SELECT PARSE_TIME_ISO8601('10:30:10')
SELECT TYPEOF(PARSE_TIME_ISO8601('10:30:10'))
Here are some examples of comparison and interval arithmetic operations on the time type:
SELECT TIME(10,31,10) > PARSE_TIME_ISO8601('10:30:10')
SELECT TIME '10:30:10' > PARSE_TIME_ISO8601('10:30:10') - INTERVAL 1 SECOND
SELECT
PARSE_TIME_ISO8601('10:31:10') AS now,
(PARSE_TIME_ISO8601('10:31:10') - INTERVAL 1 MINUTE) AS one_minute_ago
EXTRACT time
EXTRACT(part FROM time)
Extracts a component specified by part from time.
Here are supported values for part for time along with example queries.
| Type | Part | Query | Result |
|---|---|---|---|
| TIME | MICROSECOND | SELECT EXTRACT(MICROSECOND FROM TIME '04:30:20.345') | 345000 |
| TIME | MILLISECOND | SELECT EXTRACT(MILLISECOND FROM TIME '04:30:20.345') | 345 |
| TIME | SECOND | SELECT EXTRACT(SECOND FROM TIME '04:30:20.345') | 20 |
| TIME | MINUTE | SELECT EXTRACT(MINUTE FROM TIME '04:30:20.345') | 30 |
| TIME | HOUR | SELECT EXTRACT(HOUR FROM TIME '04:30:20.345') | 4 |
| TIME | DAY | SELECT EXTRACT(DAY FROM TIME '04:30:20.345') | 1 |
| TIME | DAYOFWEEK | SELECT EXTRACT(DAYOFWEEK FROM TIME '04:30:20.345') | 5 |
| TIME | DOW | SELECT EXTRACT(DOW FROM TIME '04:30:20.345') | 5 |
| TIME | DAYOFYEAR | SELECT EXTRACT(DAYOFYEAR FROM TIME '04:30:20.345') | 1 |
| TIME | DOY | SELECT EXTRACT(DOY FROM TIME '04:30:20.345') | 1 |
| TIME | MONTH | SELECT EXTRACT(MONTH FROM TIME '04:30:20.345') | 1 |
| TIME | QUARTER | SELECT EXTRACT(QUARTER FROM TIME '04:30:20.345') | 1 |
| TIME | YEAR | SELECT EXTRACT(YEAR FROM TIME '04:30:20.345') | 1970 |
| TIME | DATE | SELECT EXTRACT(DATE FROM TIME '04:30:20.345') | 1970-01-01 |
| TIME | TIME | SELECT EXTRACT(TIME FROM TIME '04:30:20.345') | 04:30:20.345 |
| TIME | EPOCH | SELECT EXTRACT(EPOCH FROM TIME '04:30:20.345') | 16220 |
| TIME | ISOWEEK | SELECT EXTRACT(ISOWEEK FROM TIME '04:30:20.345') | 1 |
| TIME | ISOYEAR | SELECT EXTRACT(ISOYEAR FROM TIME '04:30:20.345') | 1970 |