GoogleSQL for BigQuery supports the following time functions.

Function list

CURRENT_TIME

CURRENT_TIME([time_zone])

CURRENT_TIME

Description

Returns the current time as aTIME object. Parentheses are optional whencalled with no arguments.

This function supports an optionaltime_zone parameter.SeeTime zone definitions for informationon how to specify a time zone.

The current time value is set at the start of the query statement that containsthis function. All invocations ofCURRENT_TIME() within a query statementyield the same value.

Return Data Type

TIME

Example

SELECTCURRENT_TIME()asnow;/*----------------------------+ | now                        | +----------------------------+ | 15:31:38.776361            | +----------------------------*/

EXTRACT

EXTRACT(partFROMtime_expression)

Description

Returns a value that corresponds to the specifiedpart froma suppliedtime_expression.

Allowedpart values are:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR

Returned values truncate lower order time periods. For example, when extractingseconds,EXTRACT truncates the millisecond and microsecond values.

Return Data Type

INT64

Example

In the following example,EXTRACT returns a value corresponding to theHOURtime part.

SELECTEXTRACT(HOURFROMTIME"15:30:00")ashour;/*------------------+ | hour             | +------------------+ | 15               | +------------------*/

FORMAT_TIME

FORMAT_TIME(format_string,time_expr)

Description

Formats aTIME value according to the specified format string.

Definitions

• format_string: ASTRING value that contains theformat elements to use withtime_expr.
• time_expr: ATIME value that represents the time to format.

Return Data Type

STRING

Example

SELECTFORMAT_TIME("%R",TIME"15:30:00")asformatted_time;/*----------------+ | formatted_time | +----------------+ | 15:30          | +----------------*/

PARSE_TIME

PARSE_TIME(format_string,time_string)

Description

Converts aSTRING value to aTIME value.

Definitions

• format_string: ASTRING value that contains theformat elements to use withtime_string.
• time_string: ASTRING value that represents the time to parse.

Details

Each element intime_string must have a corresponding element informat_string. The location of each element informat_string must match thelocation of each element intime_string.

-- This works because elements on both sides match.SELECTPARSE_TIME("%I:%M:%S","07:30:00");-- This produces an error because the seconds element is in different locations.SELECTPARSE_TIME("%S:%I:%M","07:30:00");-- This produces an error because one of the seconds elements is missing.SELECTPARSE_TIME("%I:%M","07:30:00");-- This works because %T can find all matching elements in time_string.SELECTPARSE_TIME("%T","07:30:00");

The format string fully supports most format elements except for%P.

The following additional considerations apply when using thePARSE_TIMEfunction:

• Unspecified fields. Any unspecified field is initialized from00:00:00.0. For instance, ifseconds is unspecified then itdefaults to00, and so on.
• Whitespace. One or more consecutive white spaces in the format stringmatches zero or more consecutive white spaces in theTIME string. Inaddition, leading and trailing white spaces in theTIME string are alwaysallowed, even if they aren't in the format string.
• Format precedence. When two (or more) format elements have overlappinginformation, the last one generally overrides any earlier ones.
• Format divergence.%p can be used witham,AM,pm, andPM.

Return Data Type

TIME

Example

SELECTPARSE_TIME("%H","15")asparsed_time;/*-------------+ | parsed_time | +-------------+ | 15:00:00    | +-------------*/

SELECTPARSE_TIME('%I:%M:%S %p','2:23:38 pm')ASparsed_time;/*-------------+ | parsed_time | +-------------+ | 14:23:38    | +-------------*/

TIME

1.TIME(hour,minute,second)2.TIME(timestamp,[time_zone])3.TIME(datetime)

Description

1. Constructs aTIME object usingINT64values representing the hour, minute, and second.
2. Constructs aTIME object using aTIMESTAMP object. It supports anoptionalparameter tospecify a time zone. If notime zone is specified, the default time zone, UTC, isused.
3. Constructs aTIME object using aDATETIME object.

Return Data Type

TIME

Example

SELECTTIME(15,30,00)astime_hms,TIME(TIMESTAMP"2008-12-25 15:30:00+08","America/Los_Angeles")astime_tstz;/*----------+-----------+ | time_hms | time_tstz | +----------+-----------+ | 15:30:00 | 23:30:00  | +----------+-----------*/

SELECTTIME(DATETIME"2008-12-25 15:30:00.000000")AStime_dt;/*----------+ | time_dt  | +----------+ | 15:30:00 | +----------*/

TIME_ADD

TIME_ADD(time_expression,INTERVALint64_expressionpart)

Description

Addsint64_expression units ofpart to theTIME object.

TIME_ADD supports the following values forpart:

• MICROSECOND
• MILLISECOND
• SECOND
• MINUTE
• HOUR

This function automatically adjusts when values fall outside of the 00:00:00 to24:00:00 boundary. For example, if you add an hour to23:30:00, the returnedvalue is00:30:00.

Return Data Types

TIME

Example

SELECTTIME"15:30:00"asoriginal_time,TIME_ADD(TIME"15:30:00",INTERVAL10MINUTE)aslater;/*-----------------------------+------------------------+ | original_time               | later                  | +-----------------------------+------------------------+ | 15:30:00                    | 15:40:00               | +-----------------------------+------------------------*/

TIME_DIFF

TIME_DIFF(end_time,start_time,granularity)

Description

Gets the number of unit boundaries between twoTIME values (end_time -start_time) at a particular time granularity.

Definitions

• start_time: The startingTIME value.
• end_time: The endingTIME value.

• granularity: The time part that represents the granularity. Ifyou passed inTIME values for the first arguments,granularity canbe:

  • MICROSECOND
  • MILLISECOND
  • SECOND
  • MINUTE
  • HOUR

Details

Ifend_time is earlier thanstart_time, the output is negative.Produces an error if the computation overflows, such as if the differencein microsecondsbetween the twoTIME values overflows.

SELECTTIME"15:30:00"asfirst_time,TIME"14:35:00"assecond_time,TIME_DIFF(TIME"15:30:00",TIME"14:35:00",MINUTE)asdifference;/*----------------------------+------------------------+------------------------+ | first_time                 | second_time            | difference             | +----------------------------+------------------------+------------------------+ | 15:30:00                   | 14:35:00               | 55                     | +----------------------------+------------------------+------------------------*/

TIME_SUB(time_expression,INTERVALint64_expressionpart)

SELECTTIME"15:30:00"asoriginal_date,TIME_SUB(TIME"15:30:00",INTERVAL10MINUTE)asearlier;/*-----------------------------+------------------------+ | original_date               | earlier                | +-----------------------------+------------------------+ | 15:30:00                    | 15:20:00               | +-----------------------------+------------------------*/

TIME_TRUNC(time_value,time_granularity)

• time_value: TheTIME value to truncate.
• time_granularity: The truncation granularity for aTIME value.Time granularities can be used.

Time granularity definitions

• MICROSECOND: If used, nothing is truncated from the value.

• MILLISECOND: The nearest lesser than or equal millisecond.

• SECOND: The nearest lesser than or equal second.

• MINUTE: The nearest lesser than or equal minute.

• HOUR: The nearest lesser than or equal hour.

Details

The resulting value is always rounded to the beginning ofgranularity.

Return Data Type

TIME

Example

SELECTTIME"15:30:00"asoriginal,TIME_TRUNC(TIME"15:30:00",HOUR)astruncated;/*----------------------------+------------------------+ | original                   | truncated              | +----------------------------+------------------------+ | 15:30:00                   | 15:00:00               | +----------------------------+------------------------*/