BigQuery supports the following TIME
functions.
CURRENT_TIME
CURRENT_TIME()
Description
Returns the current time as a TIME object.
Return Data Type
TIME
Example
SELECT CURRENT_TIME() as now;
+----------------------------+
| now |
+----------------------------+
| 15:31:38.776361 |
+----------------------------+
TIME
1. TIME(hour, minute, second)
2. TIME(timestamp, [timezone])
3. TIME(datetime)
Description
- Constructs a
TIME
object usingINT64
values representing the hour, minute, and second. - Constructs a
TIME
object using aTIMESTAMP
object. It supports an optional parameter to specify a timezone. If no timezone is specified, the default timezone, UTC, is used. - Constructs a
TIME
object using aDATETIME
object.
Return Data Type
TIME
Example
SELECT
TIME(15, 30, 00) as time_hms,
TIME(TIMESTAMP "2008-12-25 15:30:00+08", "America/Los_Angeles") as time_tstz;
+----------+-----------+
| time_hms | time_tstz |
+----------+-----------+
| 15:30:00 | 23:30:00 |
+----------+-----------+
SELECT
TIME(DATETIME "2008-12-25 15:30:00.000000") AS time_dt;
+----------+
| time_dt |
+----------+
| 15:30:00 |
+----------+
TIME_ADD
TIME_ADD(time_expression, INTERVAL INT64_expr part)
Description
Adds INT64_expr
units of part
to the TIME object.
TIME_ADD
supports the following values for part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
This function automatically adjusts when values fall outside of the 00:00:00 to
24:00:00 boundary. For example, if you add an hour to 23:30:00
, the returned
value is 00:30:00
.
Return Data Types
TIME
Example
SELECT
TIME "15:30:00" as original_time,
TIME_ADD(TIME "15:30:00", INTERVAL 10 MINUTE) as later;
+-----------------------------+------------------------+
| original_time | later |
+-----------------------------+------------------------+
| 15:30:00 | 15:40:00 |
+-----------------------------+------------------------+
TIME_SUB
TIME_SUB(time_expression, INTERVAL INT_expr part)
Description
Subtracts INT64_expr
units of part
from the TIME object.
TIME_SUB
supports the following values for part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
This function automatically adjusts when values fall outside of the 00:00:00 to
24:00:00 boundary. For example, if you subtract an hour from 00:30:00
, the
returned value is 23:30:00
.
Return Data Type
TIME
Example
SELECT
TIME "15:30:00" as original_date,
TIME_SUB(TIME "15:30:00", INTERVAL 10 MINUTE) as earlier;
+-----------------------------+------------------------+
| original_date | earlier |
+-----------------------------+------------------------+
| 15:30:00 | 15:20:00 |
+-----------------------------+------------------------+
TIME_DIFF
TIME_DIFF(time_expression, time_expression, part)
Description
Returns the number of whole specified part
intervals between two
TIME objects. Throws an error if the computation overflows the result type,
such as if the difference in microseconds between the two time objects would
overflow an INT64 value.
TIME_DIFF
supports the following values for part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
Return Data Type
INT64
Example
SELECT
TIME "15:30:00" as first_time,
TIME "14:35:00" as second_time,
TIME_DIFF(TIME "15:30:00", TIME "14:35:00", MINUTE) as difference;
+----------------------------+------------------------+------------------------+
| first_time | second_time | difference |
+----------------------------+------------------------+------------------------+
| 15:30:00 | 14:35:00 | 55 |
+----------------------------+------------------------+------------------------+
TIME_TRUNC
TIME_TRUNC(time_expression, part)
Description
Truncates a TIME object to the granularity of part
.
TIME_TRUNC
supports the following values for part
:
MICROSECOND
MILLISECOND
SECOND
MINUTE
HOUR
Return Data Type
TIME
Example
SELECT
TIME "15:30:00" as original,
TIME_TRUNC(TIME "15:30:00", HOUR) as truncated;
+----------------------------+------------------------+
| original | truncated |
+----------------------------+------------------------+
| 15:30:00 | 15:00:00 |
+----------------------------+------------------------+
FORMAT_TIME
FORMAT_TIME(format_string, time_object)
Description
Formats a TIME object according to the specified format_string
. See
Supported Format Elements For TIME
for a list of format elements that this function supports.
Return Data Type
STRING
Example
SELECT FORMAT_TIME("%R", TIME "15:30:00") as formatted_time;
+----------------+
| formatted_time |
+----------------+
| 15:30 |
+----------------+
PARSE_TIME
PARSE_TIME(format_string, string)
Description
Uses a format_string
and a string to return a TIME object. See
Supported Format Elements For TIME
for a list of format elements that this function supports.
When using PARSE_TIME
, keep the following in mind:
- Unspecified fields. Any unspecified field is initialized from
00:00:00.0
. For instance, ifseconds
is unspecified then it defaults to00
, and so on. - Whitespace. One or more consecutive white spaces in the format string matches zero or more consecutive white spaces in the TIME string. In addition, leading and trailing white spaces in the TIME string are always allowed—even if they are not in the format string.
- Format precedence. When two (or more) format elements have overlapping information, the last one generally overrides any earlier ones.
Return Data Type
TIME
Example
SELECT PARSE_TIME("%H", "15") as parsed_time;
+-------------+
| parsed_time |
+-------------+
| 15:00:00 |
+-------------+
Supported format elements for TIME
Unless otherwise noted, TIME functions that use format strings support the following elements:
Format element | Description |
%H | The hour (24-hour clock) as a decimal number (00-23). |
%I | The hour (12-hour clock) as a decimal number (01-12). |
%j | The day of the year as a decimal number (001-366). |
%k | The hour (24-hour clock) as a decimal number (0-23); single digits are preceded by a space. |
%l | The hour (12-hour clock) as a decimal number (1-12); single digits are preceded by a space. |
%M | The minute as a decimal number (00-59). |
%n | A newline character. |
%P | Either am or pm. |
%p | Either AM or PM. |
%R | The time in the format %H:%M. |
%r | The 12-hour clock time using AM/PM notation. |
%S | The second as a decimal number (00-60). |
%T | The time in the format %H:%M:%S. |
%t | A tab character. |
%X | The time representation in HH:MM:SS format. |
%% | A single % character. |
%E#S | Seconds with # digits of fractional precision. |
%E*S | Seconds with full fractional precision (a literal '*'). |