TIMESTAMP and Standard SQL in Google BigQuery

0
votes

I am trying (and failing) to perform the following query in BigQuery using Standard SQL

SELECT
  user_id
FROM
  dataset.table
WHERE
  timestamp > TIMESTAMP("2017-09-18 00:00")

I constantly get the message

Query Failed
Error: Invalid timestamp: '2017-09-18 00:00'

I tried, desperate, with 2017-09-18T00:00 or like the Legacy SQL timestamp > "2017-09-18 00:00" without success.

Thanks for your help.

3
google-bigquery
What type is your timestamp column? - Tim Biegeleisen

3 Answers

6
votes

I believe a valid timestamp has a time portion with hours, minutes, and seconds. Your timestamp literal omits the seconds portion. Try including it:

SELECT
    user_id
FROM
    dataset.table
WHERE
    timestamp > TIMESTAMP('2017-09-18 00:00:00')

The reason you need to include seconds in the timestamp, and why your current timestamp makes no sense, is that BigQuery internally stores timestamps as epoch seconds. Without specifying seconds, your timestamp cannot reliably be stored.

From the documentation:

You can describe TIMESTAMP data types as either UNIX timestamps or calendar datetimes. BigQuery stores TIMESTAMP data internally as a UNIX timestamp with microsecond precision.

3
votes

As the documentation of bigquery suggests

Date and time strings

A date and time string in the format YYYY-MM-DD HH:MM:SS. The UTC and Z specifiers are supported.

You have provided 2017-09-18 00:00 which is YYYY-MM-DD HH:MM . You will need to provide 2017-09-18 00:00:00.

So your query should look as follows

SELECT
  user_id
FROM
  dataset.table
WHERE
  timestamp > TIMESTAMP("2017-09-18 00:00:00")
2
votes

Another way to write this expression without specifying the extra zeros is:

TIMESTAMP(DATE "2017-09-18")