How to format date from Firestore in BigQuery

[thawah]

Im trying to display dates from Firestore Database in BigQuery.

Here is an example of my Firestore Document👇

[![Here is an example of my Firestore Document][1]][1]

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👇

[![this query result][2]][2]

And if I write this code👇
```
date(timestamp_seconds(cast(json_extract(data, '$.f58-sodleDate') as int64))) AS dte,
```
I get this error message👇

[![error message][3]][3]

What I want for my query result is something like this👇
[![correct query result][4]][4]

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

[1]: https://i.stack.imgur.com/Rp5R0.png
[2]: https://i.stack.imgur.com/r2Ah4.png
[3]: https://i.stack.imgur.com/fcFpu.png
[4]: https://i.stack.imgur.com/ZEKXa.png

[ms4446]

To format the date from Firestore in BigQuery, you need to extract the _seconds value from the Firestore timestamp and convert it to a BigQuery TIMESTAMP type. Once you have the TIMESTAMP, you can then format it as a date.

Here's how you can achieve this:

1. Extract the _seconds value from the Firestore timestamp using the JSON_EXTRACT_SCALAR() function.
2. Convert the extracted _seconds value to a TIMESTAMP using the TIMESTAMP_SECONDS() function.
3. Format the TIMESTAMP to the desired date format using the FORMAT_DATE() function.

Here's the SQL code to achieve this:

SELECT FORMAT_DATE('%F', DATE(TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(data, '$.f58-sodleDate._seconds') AS INT64)))) AS dte
FROM your_table_name

In the above code:

• JSON_EXTRACT_SCALAR(data, '$.f58-sodleDate._seconds') extracts the _seconds value from the Firestore timestamp.
• CAST(... AS INT64) converts the extracted string value to an integer.
• TIMESTAMP_SECONDS(...) converts the integer seconds to a TIMESTAMP.
• FORMAT_DATE('%F', DATE(...)) formats the TIMESTAMP as a date in the 'YYYY-MM-DD' format.

Replace your_table_name with the name of your actual table.

This will return the date in the dte column as 2023-10-03.