如何在BigQuery中格式化Firestore中的日期

正确SQL语句 #

要将Firestore文档中f58-sodleDate字段（嵌套JSON时间戳）格式化为2023-10-3这样的日期形式，可使用以下查询：

FORMAT_DATE('%Y-%-m-%-d', DATE(TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(data, '$.f58-sodleDate._seconds') AS INT64)))) AS dte

如果接受日期部分带前导零（如2023-10-03），可以简化格式符为%Y-%m-%d：

FORMAT_DATE('%Y-%m-%d', DATE(TIMESTAMP_SECONDS(CAST(JSON_EXTRACT_SCALAR(data, '$.f58-sodleDate._seconds') AS INT64)))) AS dte

关键步骤说明 #

1. 提取秒数字符串：JSON_EXTRACT_SCALAR(data, '$.f58-sodleDate._seconds') 从嵌套的JSON对象中精准提取_seconds字段的字符串值
2. 转整数类型：CAST(...) AS INT64 将字符串格式的秒数转换为BigQuery可识别的整数
3. 转时间戳：TIMESTAMP_SECONDS(...) 以秒数为单位生成BigQuery时间戳
4. 转日期：DATE(...) 从时间戳中提取日期部分
5. 格式化输出：FORMAT_DATE 按照指定格式输出日期，其中%-m和%-d用于去除月份、日期的前导零

你之前尝试的问题分析 #

• 直接使用JSON_EXTRACT(data, '$.f58-sodleDate')得到的是完整JSON对象（{"_seconds":..., "_nanoseconds":...}），无法直接转为整数，因此报错
• JSON_VALUE或JSON_EXTRACT_SCALAR直接取$.f58-sodleDate时，因为目标是JSON对象而非标量值（字符串、数字等），所以返回null

内容的提问来源于stack exchange，提问作者Saw Tha Wah