Google BigQuery中Unix时间转datetime及Postgres语句迁移报错咨询

Unix时间戳的转换核心要看你的时间戳是秒级、毫秒级还是微秒级，BigQuery针对不同单位提供了专属转换函数，直接对应使用就行：

• 如果是秒级时间戳（比如1690000000）：

  -- 先转成TIMESTAMP类型，再转换为DATETIME
  DATETIME(TIMESTAMP_SECONDS(your_unix_timestamp))
  

• 如果是毫秒级时间戳（比如1690000000000）：

  DATETIME(TIMESTAMP_MILLIS(your_unix_timestamp))
  

• 如果是微秒级时间戳（比如1690000000000000）：

  DATETIME(TIMESTAMP_MICROS(your_unix_timestamp))
  

不确定单位的话，可以先拿一条数据测试转换结果，符合预期再批量使用。

这个问题基本是时间戳单位不匹配搞出来的，我帮你拆解清楚：

为什么会出现1970-01-01？ #

PostgreSQL的to_timestamp(created_utc)默认把输入数字当成秒级时间戳转换，但如果你的created_utc是毫秒级时间戳（13位数字），直接在BigQuery用TIMESTAMP_SECONDS(created_utc)的话，相当于把13位毫秒数当成秒数，数值要么过大（对应未来超长时间）要么过小（对应1970年初）。另外，如果直接对原始Unix时间戳数字用DATE_TRUNC，BigQuery会把它解析成「1970年以来的天数」，自然会得到异常结果。

正确的转换写法 #

先确认created_utc的时间戳单位，再对应调整：

场景1：created_utc是秒级时间戳

完全对应PostgreSQL逻辑的BigQuery语句：

DATE_TRUNC(TIMESTAMP_SECONDS(created_utc), MONTH)

场景2：created_utc是毫秒级时间戳

需要先用TIMESTAMP_MILLIS转成正确时间戳，再截断月份：

DATE_TRUNC(TIMESTAMP_MILLIS(created_utc), MONTH)

验证小技巧

可以先单独测试时间戳转换，确认结果是2014年左右再套DATE_TRUNC：

SELECT TIMESTAMP_MILLIS(created_utc) FROM your_table LIMIT 10;

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