Google BigQuery

SELECT Statements

Google BigQuery API Syntax

The Google BigQuery API offers additional SQL operators and functions. A complete list of the available syntax is located at: https://cloud.google.com/bigquery/query-reference

A SELECT statement can consist of the following basic clauses.

SELECT
INTO
FROM
JOIN
WHERE
GROUP BY
HAVING
UNION
ORDER BY
LIMIT

SELECT Syntax

The following syntax diagram outlines the syntax supported by the SQL engine of the provider:

SELECT {

[ TOP <numeric_literal> | DISTINCT ]

{

| {

<expression> [ [ AS ] <column_reference> ]

| { <table_name> | <correlation_name> } .*

} [ , ... ]

}

[ INTO csv:// [ filename= ] <file_path> [ ;delimiter=tab ] ]

{

FROM <table_reference> [ [ AS ] <identifier> ]

} [ , ... ]

[ [

INNER | { { LEFT | RIGHT | FULL } [ OUTER ] }

] JOIN <table_reference> [ ON <search_condition> ] [ [ AS ] <identifier> ]

] [ ... ]

[ WHERE <search_condition> ]

[ GROUP BY <column_reference> [ , ... ]

[ HAVING <search_condition> ]

[ UNION [ ALL ] <select_statement> ]

[

ORDER BY

<column_reference> [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ]

]

[

LIMIT <expression>

[

{ OFFSET | , }

]

} | SCOPE_IDENTITY()

<expression> ::=

| <column_reference>

| @ <parameter>

| ?

| COUNT( * | { [ DISTINCT ] <expression> } )

| { AVG | MAX | MIN | SUM | COUNT } ( <expression> )

| NULLIF ( <expression> , <expression> )

| COALESCE ( <expression> , ... )

| CASE <expression>

WHEN { <expression> | <search_condition> } THEN { <expression> | NULL } [ ... ]

[ ELSE { <expression> | NULL } ]

END

| <literal>

| <sql_function>

<search_condition> ::=

{

<expression> { = | > | < | >= | <= | <> | != | LIKE | NOT LIKE | IN | NOT IN | IS NULL | IS NOT NULL | AND | OR | CONTAINS | BETWEEN } [ <expression> ]

} [ { AND | OR } ... ]

Examples

Return all columns:
SELECT * FROM publicdata.samples.github_nested
Rename a column:
SELECT [repository.name] AS MY_repository.name FROM publicdata.samples.github_nested
Cast a column's data as a different data type:
SELECT CAST(repository.watchers AS VARCHAR) AS Str_repository.watchers FROM publicdata.samples.github_nested
Search data:
SELECT * FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework';
The Google BigQuery APIs support the following operators in the WHERE clause: =, >, <, >=, <=, <>, !=, LIKE, NOT LIKE, IN, NOT IN, IS NULL, IS NOT NULL, AND, OR.
SELECT * FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework';
Return the number of items matching the query criteria:
SELECT COUNT(*) AS MyCount FROM publicdata.samples.github_nested
Return the unique items matching the query criteria:
SELECT DISTINCT repository.name FROM publicdata.samples.github_nested
Summarize data:
SELECT repository.name, MAX(repository.watchers) FROM publicdata.samples.github_nested GROUP BY repository.name
See Aggregate Functions below for details.
Retrieve data from multiple tables.
SELECT * FROM CRMAccounts INNER JOIN ERPCustomers ON CRMAccounts.BillingState = ERPCustomers.BillingState
See JOIN Queries below for details.
Sort a result set in ascending order:
SELECT actor.attributes.email, repository.name FROM publicdata.samples.github_nested ORDER BY repository.name ASC
Restrict a result set to the specified number of rows:
SELECT actor.attributes.email, repository.name FROM publicdata.samples.github_nested LIMIT 10
Parameterize a query to pass in inputs at execution time. This enables you to create prepared statements and mitigate SQL injection attacks.
SELECT * FROM publicdata.samples.github_nested WHERE repository.name = @param

Aggregate Functions

Google BigQuery API Syntax

The Google BigQuery API offers additional SQL operators and functions. A complete list of the available syntax is located at: https://cloud.google.com/bigquery/query-reference

Examples of Aggregate Functions

Below are several examples of SQL aggregate functions. You can use these with a GROUP BY clause to aggregate rows based on the specified GROUP BY criterion. This can be a reporting tool.

COUNT

Returns the number of rows matching the query criteria.

SELECT COUNT(*) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework'

AVG

Returns the average of the column values.

SELECT repository.name, AVG(repository.watchers) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

MIN

Returns the minimum column value.

SELECT MIN(repository.watchers), repository.name FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

MAX

Returns the maximum column value.

SELECT repository.name, MAX(repository.watchers) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework' GROUP BY repository.name

SUM

Returns the total sum of the column values.

SELECT SUM(repository.watchers) FROM publicdata.samples.github_nested WHERE repository.name = 'EntityFramework'

CORR

Returns the Pearson correlation coefficient of a set of number pairs.

SELECT repository.name, CORR(repository.watchers, repository.size) FROM publicdata.samples.github_nested

COVAR_POP

Computes the population covariance of the values computed by a set of number pairs.

SELECT repository.name, COVAR_POP(repository.watchers, repository.size) FROM publicdata.samples.github_nested

COVAR_SAMP

Computes the sample covariance of the values computed by a set of number pairs.

SELECT repository.name, COVAR_SAMP(repository.watchers, repository.size) FROM publicdata.samples.github_nested

NTH

Returns the nth sequential value in the scope of the function, where n is a constant. The NTH function starts counting at 1, so there is no zeroth term. If the scope of the function has less than n values, the function returns NULL.

SELECT repository.name, NTH(n, actor.attributes.email) FROM publicdata.samples.github_nested

STDDEV

Returns the standard deviation of the computed values. Rows with a NULL value are not included in the calculation.

SELECT repository.name, STDDEV(repository.watchers) FROM publicdata.samples.github_nested

JOIN Queries

The provider supports the complete join syntax in Google BigQuery. Google BigQuery supports inner joins, outer joins, and cross joins. The default is inner. Multiple join operations are supported.

SELECT field_1 [..., field_n] FROM table_1 [[AS] alias_1] [[INNER|[FULL|RIGHT|LEFT] OUTER|CROSS] JOIN [EACH] table_2 [[AS] alias_2] [ON join_condition_1 [... AND join_condition_n]] ]+

Note that the default join is an inner join. The following limitations exist on joins in Google BigQuery:

Cross joins must not contain an ON clause.
Normal joins require that the right-side table must contain less than 8 MB of compressed data. If you are working with tables larger than 8 MB, use the EACH modifier. Note that EACH cannot be used in cross joins.

Projection Functions

ANY_VALUE(expression)

Returns any value from the input or NULL if there are zero input rows. The value returned is non-deterministic, which means you might receive a different result each time you use this function. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: The expression to retrieve a value from.

APPROX_COUNT_DISTINCT(expression)

Returns the approximate result for COUNT(DISTINCT expression). The value returned is a statistical estimate-not necessarily the actual value. This function is less accurate than COUNT(DISTINCT expression), but performs better on huge input. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: The expression to perform the approximate count distinct on.

APPROX_QUANTILES(expression, number)

Returns the approximate boundaries for a group of expression values, where number represents the number of quantiles to create. This function returns an array of number + 1 elements, where the first element is the approximate minimum and the last element is the approximate maximum. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: The expression to perform the approximate quantiles on.
number: The number of quantiles to create.

APPROX_TOP_COUNT(expression, number)

Returns the approximate top elements of expression. The number parameter specifies the number of elements returned. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: The expression to perform the approximate top count on.
number: The number of elements to be returned.

APPROX_TOP_SUM(expression, weight, number)

Returns the approximate top elements of expression, based on the sum of an assigned weight. The number parameter specifies the number of elements returned. If the weight input is negative or NaN, this function returns an error. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: The expression to perform the approximate top sum on.
weight: The assigned weight.
number: The number of elements to be returned.

ARRAY(subquery)

The ARRAY function returns an ARRAY with one element for each row in a subquery. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

subquery: The subquery to execute.

ARRAY_CONCAT(array_expr1 [, array_expr2 [, ...]])

Concatenates one or more arrays with the same element type into a single array. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

array_expr1: The first array to concatenate.
array_expr2: The second array to concatenate.

ARRAY_LENGTH(array_expr)

Returns the size of the array. Returns 0 for an empty array. Returns NULL if the array_expression is NULL. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

array_expr: The array expression to get the size of.

ARRAY_TO_STRING(array_expr, delimiter [, null_text])

Returns a concatenation of the elements in array_expression as a STRING. The value for array_expression can either be an array of STRING or BYTES data types. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

array_expr: The array expression to convert to string.
delimiter: The delimiter string used to delimit the array values.
null_text: If the null_text parameter is used, the function replaces any NULL values in the array with the value of null_text. If the null_text parameter is not used, the function omits the NULL value and its preceding delimiter.

GENERATE_ARRAY(start_expr, end_expr [, step_expr])

Returns an array of values. The start_expression and end_expression parameters determine the inclusive start and end of the array. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

start_expr: The starting value.
end_expr: The ending value.
step_expr: The increment used to generate array values.

GENERATE_DATE_ARRAY(start_date, end_date [, INTERVAL int_expr date_part])

Returns an array of dates. The start_date and end_date parameters determine the inclusive start and end of the array. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

start_date: The starting date.
end_date: The ending date.
int_expr: The increment used to generate dates.
date_part: The date part used to increment the generated dates. Valid values are: DAY, WEEK, MONTH, QUARTER, and YEAR.

ARRAY_REVERSE(array_expr)

Returns the input ARRAY with elements in reverse order. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

array_expr: The array to reverse.

ARRAY_AGG(expression)

Returns an ARRAY of expression values. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: The expression values to generate an array from.

ARRAY_CONCAT_AGG(expression1[, expression2 [,...]])

Concatenates elements from expression of type ARRAY, returning a single ARRAY as a result. This function ignores NULL input arrays, but respects the NULL elements in non-NULL input arrays (an error is raised, however, if an array in the final query result contains a NULL element). Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression1: The first expression to concatenate.
expression2: The first expression to concatenate.

AVG([DISTINCT] expression)

Returns the average on non-null values. Each distinct value of expression is aggregated only once into the result.

expression: The expression to use to compute the average.

BIT_AND(numeric_expression)

Returns the result of a bitwise AND operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.

numeric_expression: The numeric expression to perform the bitwise operation.

BIT_COUNT(expression)

The input, expression, must be an integer or BYTES. Returns the number of bits that are set in the input expression. For integers, this is the number of bits in two's complement form. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: The expression to perform the bit count operation on.

BIT_OR(numeric_expression)

Returns the result of a bitwise OR operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.

numeric_expression: The numeric expression to perform the bitwise operation.

BIT_XOR(numeric_expression)

Returns the result of a bitwise XOR operation between each instance of numeric_expr across all rows. NULL values are ignored. This function returns NULL if all instances of numeric_expr evaluate to NULL.

numeric_expression: The numeric expression to perform the bitwise operation.

CORR(numeric_expression1, numeric_expression2)

Returns the Pearson correlation coefficient of a set of number pairs.

numeric_expression1: The first series.
numeric_expression2: The second series.

COUNTIF(expression)

Returns the count of TRUE values for expression. Returns 0 if there are zero input rows or expression evaluates to FALSE for all rows. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: The expression to evaluate.

COVAR_POP(numeric_expression1, numeric_expression2)

Computes the population covariance of the values computed by numeric_expression1 and numeric_expression2.

numeric_expression: The first series.
numeric_expression: The second series.

COVAR_SAMP(numeric_expression1, numeric_expression2)

Computes the sample covariance of the values computed by numeric_expression1 and numeric_expression2.

numeric_expression: The first series.
numeric_expression: The second series.

FIRST(column)

Returns the first sequential value in the scope of the function. Note: this function is only available when UseLegacySQL=True.

column: Any column expression.

FIRST_VALUE(value_expression [(IGNORE/RESPECT) NULLS])

Returns the value of the value_expression for the first row in the current window frame. Note: this function only supports [IGNORE NULLS] when using Standard SQL (UseLegacySQL=False).

value_expression: Any value expression

GROUP_CONCAT(string_expression [, separator])

Concatenates multiple strings into a single string, where each value is separated by the optional separator parameter. If separator is omitted, returns a comma-separated string. Note: this function is only available when UseLegacySQL=True.

string_expression: The string expression to concat.
separator: The separator.

GROUP_CONCAT_UNQUOTED(string_expression [, separator])

Concatenates multiple strings into a single string, where each value is separated by the optional separator parameter. If separator is omitted, BigQuery returns a comma-separated string. Unlike GROUP_CONCAT, this function will not add double quotes to returned values that include a double quote character. For example, the string a"b would return as a"b. Note: this function is only available when UseLegacySQL=True.

string_expression: The string expression to concat.
separator: The separator.

LAST(column)

Returns the last sequential value in the scope of the function. Note: this function is only available when UseLegacySQL=True.

column: Any column expression

LAST_VALUE(value_expression [(IGNORE/RESPECT) NULLS])

Returns the value of the value_expression for the last row in the current window frame. Note: this function only supports [IGNORE NULLS] when using Standard SQL (UseLegacySQL=False).

value_expression: Any value expression

LOGICAL_AND(expression)

Returns the logical AND of all non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: The expression to perform the logical AND on.

LOGICAL_OR(expression)

Returns the logical OR of all non-NULL expressions. Returns NULL if there are zero input rows or expression evaluates to NULL for all rows. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: The expression to perform the logical OR on.

NEST(expression)

Aggregates all values in the current aggregation scope into a repeated field. For example, the query SELECT x, NEST(y) FROM ... GROUP BY x returns one output record for each distinct x value, and contains a repeated field for all y values paired with x in the query input. The NEST function requires a GROUP BY clause. Note: this function is only available when UseLegacySQL=True.

expression: Any expression.

NOW()

Returns the current UNIX timestamp in microseconds. Note: this function is only available when UseLegacySQL=True.

NTH(n, field)

n: The nth sequential value.
field: The column name.

NTH_VALUE(value_expression, constant_integer_expression)

Returns the value of value_expression at the Nth row of the current window frame, where Nth is defined by constant_integer_expression. Returns NULL if there is no such row. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

value_expression: Any value expression.
constant_integer_expression: The nth sequential value.

QUANTILES(expression [, buckets])

Computes approximate minimum, maximum, and quantiles for the input expression. NULL input values are ignored. Empty or exclusively-NULL input results in NULL output. The number of quantiles computed is controlled with the optional buckets parameter, which includes the minimum and maximum in the count. Note: this function is only available when UseLegacySQL=True.

expression: The numeric expression to compute quantiles on.
buckets: The number of buckets.

STDDEV(numeric_expression)

Returns the standard deviation of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.

numeric_expression: The series to calculate STDDEV on.

STDDEV_POP(numeric_expression)

Computes the population standard deviation of the value computed by numeric_expr.

numeric_expression: The series to calculate STDDEV on.

STDDEV_SAMP([DISTINCT] numeric_expression)

Computes the sample standard deviation of the value computed by numeric_expr.

numeric_expression: The series to calculate STDDEV on.

STRING_AGG(expression[, delimiter])

Returns a value (either STRING or BYTES) obtained by concatenating non-null values. If a delimiter is specified, concatenated values are separated by that delimiter; otherwise, a comma is used as a delimiter. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: The string expression to concatenate.
delimiter: The delimiter to separate concatenated values.

SUM([DISTINCT] expression)

Returns the sum on non-null values. Each distinct value of expression is aggregated only once into the result.

expression: The expression to use to compute the sum.

TOP(column [, max_values][, multiplier])

TOP is a function that is an alternative to the GROUP BY clause. It is used as simplified syntax for GROUP BY ... ORDER BY ... LIMIT .... Generally, the TOP function performs faster than the full ... GROUP BY ... ORDER BY ... LIMIT ... query, but may only return approximate results. Note: this function is only available when UseLegacySQL=True.

numeric_expression: The series to calculate STDDEV on.
max_values: The maximum number of results to return. Default is 20.
multiplier: A positive integer that increases the value(s) returned by COUNT(*) by the multiple specified.

UNIQUE(expression)

Returns the set of unique, non-NULL values in the scope of the function in an undefined order. Note: this function is only available when UseLegacySQL=True.

expression: Any expression.

VARIANCE(numeric_expression)

Computes the variance of the values computed by numeric_expr. Rows with a NULL value are not included in the calculation.

numeric_expression: The series to calculate VARIANCE on.

VAR_POP(numeric_expression)

Computes the population variance of the values computed by numeric_expr.

numeric_expression: The series to calculate VARIANCE on.

VAR_SAMP([DISTINCT] numeric_expression)

Computes the sample variance of the values computed by numeric_expr.

numeric_expression: The series to calculate VARIANCE on.

RANK()

Returns the ordinal (1-based) rank of each row within the ordered partition. All peer rows receive the same rank value. The next row or set of peer rows receives a rank value which increments by the number of peers with the previous rank value, instead of a rank value which always increments by 1. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

PERCENT_RANK()

Return the percentile rank of a row defined as (RK-1)/(NR-1), where RK is the RANK of the row and NR is the number of rows in the partition. Returns 0 if NR=1. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

NTILE(constant_integer_expression)

This function divides the rows into constant_integer_expression buckets based on row ordering and returns the 1-based bucket number that is assigned to each row. The number of rows in the buckets can differ by at most 1. The remainder values (the remainder of number of rows divided by buckets) are distributed one for each bucket, starting with bucket 1. If constant_integer_expression evaluates to NULL, 0 or negative, an error is provided. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

constant_integer_expression: The number of buckets to divide the rows into.

LEAD(value_expression [, offset [, default_expression]])

Returns the value of the value_expression on a subsequent row. Changing the offset value changes which subsequent row is returned; the default value is 1, indicating the next row in the window frame. An error occurs if offset is NULL or a negative value. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

value_expression: The value expression.
offset: The offset to use. Must be a non-negative integer.
default_expression: The default expression. Must be compatible with the value_expression type.

LAG(value_expression [, offset [, default_expression]])

value_expression: The value expression.
offset: The offset to use. Must be a non-negative integer.
default_expression: The default expression. Must be compatible with the value_expression type.

PERCENTILE_CONT(value_expression [, percentile [{RESPECT | IGNORE} NULLS]])

Computes the specified percentile value for the value_expression, with linear interpolation. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

value_expression: A numeric expression.
percentile: A literal in the range [0, 1].

PERCENTILE_DISC(value_expression, percentile [{RESPECT | IGNORE} NULLS])

Computes the specified percentile value for a discrete value_expression. The returned value is the first sorted value of value_expression with cumulative distribution greater than or equal to the given percentile value. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

value_expression: Any orderable type.
percentile: A literal in the range [0, 1].

COALESCE(expr1 [, expr2 [, ...]])

Returns the value of the first non-null expression. The remaining expressions are not evaluated. All input expressions must be implicitly coercible to a common supertype. Note: this function currently accepts up to 9 expressions.

expr1: Any expression
expr2: Any expression

NULLIF(expression, expression_to_match)

Returns NULL if expression = expression_to_match is true, otherwise returns expression. expression and expression_to_match must be implicitly coercible to a common supertype; equality comparison is done on coerced values. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

expression: Any expression
expression_to_match: Any expression to be matched

CUME_DIST()

Return the relative rank of a row defined as NP/NR. NP is defined to be the number of rows that either precede or are peers with the current row. NR is the number of rows in the partition. Note: this function returns a double when using Legacy SQL (UseLegacySQL=True).

DENSE_RANK()

Returns the ordinal (1-based) rank of each row within the window partition. All peer rows receive the same rank value, and the subsequent rank value is incremented by one.

ROW_NUMBER()

Does not require the ORDER BY clause. Returns the sequential row ordinal (1-based) of each row for each ordered partition. If the ORDER BY clause is unspecified then the result is non-deterministic.

IFNULL(expr, null_result)

If expr is NULL, return null_result. Otherwise, return expr. If expr is not NULL, null_result is not evaluated. expr and null_result must be implicitly coercible to a common supertype. Synonym for COALESCE(expr, null_result)

expr: Any expression
null_result: The result to return if expr is null

CAST(expression AS type)

Cast is used in a query to indicate that the result type of an expression should be converted to some other type.

expression: The expression to cast.
type: The type to cast the expression to.

SAFE_CAST(expression)

Cast is used in a query to indicate that the result type of an expression should be converted to some other type.SAFE_CAST is identical to CAST, except it returns NULL instead of raising an error.

expression: The expression to cast.

CURRENT_DATE()

Returns a human-readable string of the current date in the format %Y-%m-%d.

DATE(timestamp [, timezone])

Converts a timestamp_expression to a DATE data type. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

timestamp: The timestamp from which to return the date.
timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

DATEDIFF(timestamp1, timestamp2)

Returns the number of days between two TIMESTAMP data types. The result is positive if the first TIMESTAMP data type comes after the second TIMESTAMP data type, and otherwise the result is negative. Note: this function is only available when UseLegacySQL=True.

timestamp1: The first timestamp.
timestamp2: The second timestamp.

DATE_DIFF(date1, date2, date_part)

Computes the number of specified date_part differences between two date expressions. This can be thought of as the number of date_part boundaries crossed between the two dates. If the first date occurs before the second date, then the result is negative. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

date1: The first date.
date2: The second date.
date_part: The date part. Supported values are: DAY, MONTH, QUARTER, YEAR.

DATE_TRUNC(date, date_part)

Truncates the date to the specified granularity. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

date: The date to truncate.
date_part: The date part. Supported values are: DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, ISOYEAR.

FORMAT_DATE(format_string, date_expr)

Formats the date_expr according to the specified format_string. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

format_string: The format string used to format the date_expr.
date_expr: The date to format.

PARSE_DATE(format_string, date_string)

Uses a format_string and a string representation of a date to return a DATE object. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

format_string: The format string used to parse the date_string.
date_string: The date string to parse.

CURRENT_DATETIME([timezone])

Returns the current time as a DATETIME object.

timezone: The timezone to use when retrieving the current datetime object.

DATETIME(timestamp [, timezone])

Constructs a DATETIME object using a TIMESTAMP object. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

timestamp: The timestamp from which to return the datetime.
timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

DATETIME_DIFF(datetime1, datetime2, date_part)

datetime1: The first datetime.
datetime2: The second datetime.
date_part: The date part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, MONTH, QUARTER, and YEAR.

DATETIME_TRUNC(datetime, part)

Truncates the datetime to the specified granularity. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

date: The datetime to truncate.
part: The date part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR, DAY, WEEK, ISOWEEK, MONTH, QUARTER, YEAR, and ISOYEAR.

FORMAT_DATETIME(format_string, datetime_expr)

Formats the date_expr according to the specified format_string. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

format_string: The format string used to format the date_expr.
datetime_expr: The datetime to format.

PARSE_DATETIME(format_string, datetime_string)

Uses a format_string and a string representation of a date to return a DATETIME object. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

format_string: The format string used to parse the date_string.
datetime_string: The datetime string to parse.

CURRENT_TIME()

Returns a human-readable string of the server's current time in the format %H:%M:%S.

TIME(timestamp [, timezone])

Constructs a DATETIME object using a TIMESTAMP object. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

timestamp: The timestamp from which to return the datetime.
timezone: The timezone to use when converting the timestamp. If not specified, the default timezone, UTC, is used.

TIME_DIFF(time1, time2, time_part)

Computes the number of specified time_part differences between two time expressions. This can be thought of as the number of time_part boundaries crossed between the two times. If the first time occurs before the second time, then the result is negative. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

time1: The first time.
time2: The second time.
time_part: The time part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR.

TIME_TRUNC(time, part)

Truncates the time to the specified granularity. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

time: The time to truncate.
part: The time part. Possible values include: MICROSECOND, MILLISECOND, SECOND, MINUTE, HOUR.

FORMAT_TIME(format_string, time_expr)

Formats the date_expr according to the specified format_string. Note: this function is only available when using Standard SQL (UseLegacySQL=False).

format_string: The format string used to format the date_expr.
time_expr: The time to format.