BigQuery中如何将字符串列转换为datetime类型？

BigQuery不支持CONVERT函数（这是SQL Server等数据库的语法），要将你的字符串格式日期转为Datetime类型，需要用BigQuery的PARSE_DATETIME函数，结合匹配你数据格式的格式字符串。

第一步：转换字符串为Datetime #

你的日期格式是MM/DD/YYYY HH:MI:SS AM/PM，对应的格式字符串是'%m/%d/%Y %I:%M:%S %p'，正确的查询语句如下：

SELECT 
  date_hour,
  -- 转换字符串为Datetime
  PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', date_hour) AS converted_datetime
FROM `turing-bebop-390023.Fitness_tracker_data.hourly_steps` 
LIMIT 1000

如果担心部分行格式错误导致查询失败，可以用SAFE.PARSE_DATETIME，错误行的转换结果会返回NULL而不是中断查询：

SELECT 
  date_hour,
  SAFE.PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', date_hour) AS converted_datetime
FROM `turing-bebop-390023.Fitness_tracker_data.hourly_steps` 
LIMIT 1000

第二步：按星期几汇总数据 #

基于转换后的Datetime，你可以用FORMAT_DATE获取星期几的名称，或者EXTRACT(DAYOFWEEK)获取星期数字，再进行分组汇总。示例如下（假设你要统计每天的记录数，可替换为你需要的汇总字段，比如SUM(steps)）：

SELECT
  -- 获取星期几的全名（如Monday、Tuesday），用'%a'可获取缩写（Mon、Tue）
  FORMAT_DATE('%A', DATE(PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', date_hour))) AS weekday_name,
  COUNT(*) AS record_count
FROM `turing-bebop-390023.Fitness_tracker_data.hourly_steps`
GROUP BY weekday_name
-- 按星期顺序排序（BigQuery中DAYOFWEEK周日=1，周六=7）
ORDER BY EXTRACT(DAYOFWEEK FROM PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', date_hour))

如果希望按周一到周日的顺序排序，可以调整ORDER BY部分：

ORDER BY 
  CASE EXTRACT(DAYOFWEEK FROM PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', date_hour))
    WHEN 1 THEN 7
    ELSE EXTRACT(DAYOFWEEK FROM PARSE_DATETIME('%m/%d/%Y %I:%M:%S %p', date_hour)) - 1
  END

内容的提问来源于stack exchange，提问作者Leah Stortvedt