CODEWITHSUNDEEP
google-bigquery
DiSt8885
DiSt8885

Reputation: 113

how to find data based on exact timestamp

I'm trying to grab a data row in BigQuery by timestamp '2018-12-08 00:00:42.808 America/Los_Angeles'. This works with between clause. For example, timestamp BETWEEN '....00:00:42.808...' AND '....00:00:42.809...'.

However, I'm not able to find anything when I just want to do timestamp = '....00:00:42.808...'. I'm not sure why this is and I can't seem to find much answer on google for this particular case.

Upvotes: 1

Views: 176

Answers (1)

Veikko
Veikko

Reputation: 3610

The timestamp in Google BigQuery is quite exact, so your query probably does not EXACTLY hit the timestamps in your table. You can use TIMESTAMP_TRUNC -function if you want to hit timestamp at millisecond, second or any rounded level. With this function you can have a where clause like this:

where TIMESTAMP_TRUNC(timestamp, millisecond, 'America/Los_Angeles')='2018-12-08 00:00:42.808 America/Los_Angeles'

This would give you the result at millisecond level you expect. You can find more information on TIMESTAMP_TRUNC and other BigQuery functions from https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators.

Upvotes: 1

Related Questions