BigQuery中从Timestamp/Date提取Epoch的方法（对应PostgreSQL实现）

嘿，这个问题我经常碰到，刚好能给你捋清楚BigQuery里的等效操作！

PostgreSQL里的extract(epoch from timestamp_column)返回的是从1970-01-01 00:00:00 UTC到目标时间的秒数（可以带小数，包含毫秒/微秒）。在BigQuery里，你可以根据精度需求用下面几个函数：

• 整数秒精度：用UNIX_SECONDS()函数，直接返回INT64类型的秒数：

  SELECT UNIX_SECONDS(timestamp_column) AS epoch_seconds
  FROM your_dataset.your_table;
  

• 毫秒精度的秒数：如果需要保留毫秒（和PostgreSQL返回的带小数结果一致），可以用UNIX_MILLIS()除以1000：

  SELECT UNIX_MILLIS(timestamp_column) / 1000.0 AS epoch_seconds_with_ms
  FROM your_dataset.your_table;
  

• 微秒精度的秒数：要保留微秒的话，用UNIX_MICROS()除以1e6：

  SELECT UNIX_MICROS(timestamp_column) / 1000000.0 AS epoch_seconds_with_us
  FROM your_dataset.your_table;
  

如果你的输入是DATE类型、日期字符串，甚至是不同时区的日期，需要先转换成TIMESTAMP，再用上面的UNIX_*函数：

• 从DATE类型转换：DATE类型默认会被转换成UTC的0点TIMESTAMP，再提取秒数：

  SELECT UNIX_SECONDS(TIMESTAMP(date_column)) AS epoch_seconds
  FROM your_dataset.your_table;
  

• 从日期字符串转换：直接把字符串转成TIMESTAMP再处理，如果是UTC时间：

  SELECT UNIX_SECONDS(TIMESTAMP('2023-10-01 14:25:00')) AS epoch_utc;
  

  如果是其他时区的日期字符串，记得指定时区参数，避免转换错误：

  SELECT UNIX_SECONDS(TIMESTAMP('2023-10-01 09:25:00', 'America/Chicago')) AS epoch_utc;
  

  上面的例子会把芝加哥时区的时间转换成UTC后再计算Epoch值。

补充个小细节：PostgreSQL的extract(epoch ...)返回的是numeric类型，而BigQuery的UNIX_SECONDS/UNIX_MILLIS/UNIX_MICROS都是返回INT64，所以如果需要和PostgreSQL完全一致的带小数结果，一定要做除法转换哦！

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