Firestore で吐き出された秒単位のタイムスタンプを日付に変換するSQLクエリ

下記の記事で扱ったように、Export Collections to BigQuery の拡張機能でデータを Firestore から吐き出すと、BigQuery には “data” というフィールドに JSON 文字列の形でデータが格納されます。

Export Collections to BigQuery で Firestore のデータを BigQuery へ自動エクスポート

2021.10.04

その中で、ネストされて取り出しにくい形で timestamp が入っていました。

{“timestamp”:{“_seconds”:1603085609,”_nanoseconds”:403000000}}

timestamp は時間単位が秒のものが “_seconds” にありますので、まずはこれを取り出し、日付に変換する必要があります。

まず、”_seconds” を取り出すには、JSON_EXTRACTJSON_QUERY を組み合わせて次のように書きます。

JSON_EXTRACT では返り値が文字列となってしまうので、はじめに JSON_QUERY でJSON形式のstringとしてネストされた中身を取り出しておきましょう。

これで取り出された秒単位のタイムスタンプは文字列ですので、一度 INT64 にキャストしてから日付に変換すれば完了です。

 

おわり