BigQuery中TIMESTAMP转换后显示未来12年日期的问题求助

问题描述 #

执行以下查询时，返回的event_date为20240318，但通过DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Karachi")转换得到的datetime_tstz却显示为2036-06-03T07:06:23.005627，且已禁用查询缓存。

查询代码：

select 
  event_date,
  DATETIME(TIMESTAMP_MICROS(event_timestamp), "Asia/Karachi") as datetime_tstz
from 
`app.analytics_317927526.events_intraday_*`
where
event_timestamp=(select max(event_timestamp) from `app.analytics_317927526.events_intraday_*`)

问题原因 #

核心原因是时间戳单位不匹配：

• TIMESTAMP_MICROS()函数要求输入的是微秒级时间戳（1秒=1,000,000微秒）
• GA4的event_timestamp字段实际存储的是毫秒级时间戳（1秒=1,000毫秒）。误用TIMESTAMP_MICROS()处理毫秒级时间戳，会导致时间被放大1000倍，最终得到超前几十年的错误日期。

解决方案 #

将转换函数替换为对应毫秒级的TIMESTAMP_MILLIS()，或者将event_timestamp除以1000后再用TIMESTAMP_MICROS()：

方案1：使用TIMESTAMP_MILLIS() #

select 
  event_date,
  DATETIME(TIMESTAMP_MILLIS(event_timestamp), "Asia/Karachi") as datetime_tstz
from 
`app.analytics_317927526.events_intraday_*`
where
event_timestamp=(select max(event_timestamp) from `app.analytics_317927526.events_intraday_*`)

方案2：将时间戳转换为微秒后再处理 #

select 
  event_date,
  DATETIME(TIMESTAMP_MICROS(event_timestamp / 1000), "Asia/Karachi") as datetime_tstz
from 
`app.analytics_317927526.events_intraday_*`
where
event_timestamp=(select max(event_timestamp) from `app.analytics_317927526.events_intraday_*`)

验证说明 #

执行上述修改后的查询，datetime_tstz将返回与event_date匹配的正确日期（2024-03-18相关的时间）。

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