How to format date from Firestore in BigQuery

Saw Thawah — Wed, 18 Oct 2023 13:56:04 +0000

Im trying to display dates from Firestore Database in BigQuery.

Here is an example of my Firestore Document👇

I would like to display this timestamp, "f58-sodleDate", in BigQuery as 2023-10-3.

In BigQuery Console, I wrote this code 👇

json_extract(data, '$.f58-sodleDate'),

and I get this 👉 {"_seconds":1691471596,"_nanoseconds":483000000}

And if I write this code, 👇

parse_datetime("%c", JSON_VALUE(DATA, '$.f58-sodleDate')) as dte,

// or this code

JSON_EXTRACT_SCALAR(DATA, '$.f58-sodleDate') AS dte,

I get the query result as null, like this👇

And if I write this code👇

date(timestamp_seconds(cast(json_extract(data, '$.f58-sodleDate') as int64))) AS dte,

I get this error message👇

What I want for my query result is something like this👇

I achieved this by writing this code 👇

FORMAT_DATE('%F', timestamp) as dte,

Please tell me what I have to do to achieve the result I want..

Thank you, best. - STW

DEV Community: Saw Thawah