CODEWITHSUNDEEP
sqlgoogle-cloud-firestoregoogle-bigquerylooker-studio
vonUbisch
vonUbisch

Reputation: 1469

Format Firestore timestamps in BigQuery SQL for Data Studio

We're importing our Firestore data into BigQuery to generate reports in Data Studio.

Following works for SQL timestamps, but not for Firestore-JSON timestamps.

SELECT 
    PARSE_TIMESTAMP('%Y%m%d', JSON_VALUE(`data`, "$.updated_at")) AS `updated_at`
FROM 
    `project.firestore_export.profiles_raw_latest`

Our dates are in a Firestore timestamp format in the column data in a JSON field called updated_at.

How do I get a usable date format to ultimately use in Data Studio?

Edit: When I query the field without JSON_VALUE it returns as null, it's a standard timestamp format in Firestore. When I preview the data in BigQuery it returns as a JSON object: {"_seconds":1569585420,"_nanoseconds":586000000}

Upvotes: 3

Views: 1705

Answers (1)

Yun Zhang
Yun Zhang

Reputation: 5518

Assuming you don't need subsecond precision, then you can use:

#standardSQL
with sample_table as (
  select '{"_seconds":1569585420,"_nanoseconds":586000000}' as ts
  )
select PARSE_DATETIME("%s", JSON_EXTRACT_SCALAR(ts, "$['_seconds']"))
from sample_table;

Output:

+---------------------+
|         f0_         |
+---------------------+
| 2019-09-27T11:57:00 |
+---------------------+

If you do need more precision in your timestamp, you can use timestamp_micros().

Upvotes: 5

Related Questions