BigQuery: Syntax error: Unexpected keyword LEFT

Question

I got this error of "Syntax error: Unexpected keyword LEFT" from the following SQL (standard SQL) in BigQuery:

select left(cast(ts as string), 16) from temp.loc limit 1;

"ts" is a timestamp field and I wanted to get upto minutes of timestamp. Any idea?

Answer 1

For those looking for how to get the left-most characters in a string: Use LPAD instead of LEFT.

Example:

SELECT 
  LPAD('Hello', 3)

returns 'Hel'

Answer 2

If you want to extract the minutes from a timestamp field, use EXTRACT - https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#extract_1

EXTRACT(minutes from ts) as minutes

Answer 3

Left isn't a function in Standard SQL. Try using substr instead

SUBSTR