Bigquery suggest me 'EXTRACT' funtion syntax:
extract(datetime_part FROM timestamp_expr [AT TIME ZONE tz_spec])
However, Bigquery notice "Unexpected keyword AT". Moreover, it's syntax in official Bigquery document is totally different. Similar case with 'Date' function. Can anyone explain this? What should i fill in syntax is suggested by BQ? Does have any similar cases? Thanks you!!
An clear explaination.
The allowed syntax depends on whether the operand is a DATE, a DATETIME or a TIMESTAMP.
For a TIMESTAMP:
EXTRACT(part FROM timestamp_expression [AT TIME ZONE time_zone])
https://cloud.google.com/bigquery/docs/reference/standard-sql/timestamp_functions#extract
For a DATE or DATETIME:
EXTRACT(part FROM date_expression)
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#extract
For a TIMESTAMP:
DATE(timestamp_expression [, time_zone_expression])
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
(It's a bit confusing that they have this under "date functions" and not "timestamp functions", because while the result is a date, the function is applied on a timestamp and should hence be put in that chapter.)
For a DATETIME:
DATE(datetime_expression)
https://cloud.google.com/bigquery/docs/reference/standard-sql/date_functions#date
AT TIME ZONE to be a TIMESTAMP operator which you can apply to a timestamp in any situation (and hence thought I could look this up in cloud.google.com/bigquery/docs/reference/standard-sql/operators). Obviously, this isn't the case. AT TIME ZONE is not an operator unfortunately, but part of the respective function syntax, and with EXTRACT you can use this syntax, while with DATE you must state the time zone as a parameter instead. A bit of a weakness in design.
AT TIME ZONE tz_specis optional.EXTRACTsyntax... Would they look for a topic titled "weird syntax"? Probably not.