在BigQuery SQL for Data Studio中格式化FiRestore时间戳 [英] Format Firestore timestamps in BigQuery SQL for Data Studio

问题描述

我们正在将FiRestore数据导入BigQuery以在Data Studio中生成报表。

以下适用于SQL时间戳，但不适用于FiRestore-JSON时间戳。

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

我们的日期在名为updated_at的JSON字段的data列中采用FiRestoretimestamp格式。

如何获取最终在Data Studio中使用的可用日期格式？

编辑：当我查询没有JSON_VALUE的字段时，它返回为null，这是FiRestore中的标准时间戳格式。当我在BigQuery中预览数据时，它以JSON对象的形式返回：{"_seconds":1569585420,"_nanoseconds":586000000}

推荐答案

假设您不需要亚秒级精度，则可以使用：

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

输出：

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

如果您确实需要更精确的时间戳，可以使用timestamp_micros()。

这篇关于在BigQuery SQL for Data Studio中格式化FiRestore时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！