BigQuery两表日期字段格式统一与联合视图创建需求

核心思路 #

将两张表的Eventdate、LoadDate统一转换为**YYYY-MM-DD 00:00:00格式的DATETIME类型**，再通过UNION ALL合并创建视图，满足指定查询需求。

单表日期转换逻辑 #

表1（STRING类型日期） #

原格式：2023-07-07 06:31:01.623000000

• 先用PARSE_DATETIME解析字符串为DATETIME（%E*S适配纳秒级精度）
• 提取日期部分后再转为DATETIME，得到YYYY-MM-DD 00:00:00格式

转换代码示例：

CAST(DATE(PARSE_DATETIME('%F %H:%M:%E*S', Eventdate)) AS DATETIME) AS Event_date

表2（DATETIME类型日期） #

原格式：2022-02-02T07:59:15

• 直接提取日期部分后转为DATETIME即可

转换代码示例：

CAST(DATE(Eventdate) AS DATETIME) AS Event_date

创建联合视图完整SQL #

替换your-project.dataset为实际项目和数据集名称，确保两张表的字段（如city）一致：

CREATE OR REPLACE VIEW `your-project.dataset.unified_events_view` AS
-- 处理表1
SELECT
  CAST(DATE(PARSE_DATETIME('%F %H:%M:%E*S', Eventdate)) AS DATETIME) AS Event_date,
  CAST(DATE(PARSE_DATETIME('%F %H:%M:%E*S', LoadDate)) AS DATETIME) AS Load_date,
  city,
  -- 保留其他需要的字段，需与表2字段一致
FROM `your-project.dataset.table1`
UNION ALL
-- 处理表2
SELECT
  CAST(DATE(Eventdate) AS DATETIME) AS Event_date,
  CAST(DATE(LoadDate) AS DATETIME) AS Load_date,
  city,
  -- 保留其他需要的字段，需与表1字段一致
FROM `your-project.dataset.table2`

查询验证 #

视图创建完成后，即可执行指定查询：

SELECT distinct city FROM `your-project.dataset.unified_events_view` WHERE Event_date = '2023-07-07'

BigQuery会自动将字符串'2023-07-07'隐式转换为2023-07-07 00:00:00的DATETIME，匹配视图中的字段值。

内容的提问来源于stack exchange，提问作者Nelson Romero