如何在 BigQuery 的表中显示时间戳类型列的时间？

debugcn 发表于 Dev

未知

我有一个名为“INTERACTION_TIMESTAMP”的列的表。

我想显示该特定列的日期和时间格式。

到目前为止，我已经尝试了以下代码：

SELECT
 USERID as UserId,
 INTERACTION_TIMESTAMP,
 EXTRACT(ISOWEEK FROM INTERACTION_TIMESTAMP) as IsoWeek,
 EXTRACT(DATE from INTERACTION_TIMESTAMP) as date,
 EXTRACT(DAYOFWEEK FROM INTERACTION_TIMESTAMP)-1 as DayOfWeek,
 PARSE_TIMESTAMP("%H", INTERACTION_TIMESTAMP, "UTC") as Time;
 CONCAT(CAST(JOURNEY_ID AS STRING),"-", CAST(JOURNEY_ACTIONID AS STRING)) as JobId,
CASE 
 WHEN INTERACTION_TYPE = 0 THEN "open"
 WHEN INTERACTION_TYPE = 1 THEN "click"
 ELSE null
END AS Open_Click
FROM
 `XXXXXXXXX.database_name`

但是，BigQuery 总是从该行代码中显示错误消息： PARSE_TIMESTAMP("%H", INTERACTION_TIMESTAMP, "UTC") as Time;

错误消息说（据我所知）PARSE_TIMESTAMP与本网站中显示的语法不匹配：PARSE_TIMESTAMP 文档

如何处理来自INTERACTION_TIMESTAMPBigQuery的字符串，以便显示来自时间戳的时间值？

谢谢

米哈伊尔·贝里安特

下面是 BigQuery 标准 SQL

而不是PARSE_TIMESTAMP你应该使用FORMAT_TIMESTAMP

SELECT
 USERID as UserId,
 INTERACTION_TIMESTAMP,
 EXTRACT(ISOWEEK FROM INTERACTION_TIMESTAMP) as IsoWeek,
 EXTRACT(DATE from INTERACTION_TIMESTAMP) as date,
 EXTRACT(DAYOFWEEK FROM INTERACTION_TIMESTAMP)-1 as DayOfWeek,
 FORMAT_TIMESTAMP("%H", INTERACTION_TIMESTAMP, "UTC") as Time,
 CONCAT(CAST(JOURNEY_ID AS STRING),"-", CAST(JOURNEY_ACTIONID AS STRING)) as JobId,
CASE 
 WHEN INTERACTION_TYPE = 0 THEN "open"
 WHEN INTERACTION_TYPE = 1 THEN "click"
 ELSE null
END AS Open_Click
FROM
 `XXXXXXXXX.database_name`

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。