BigQuery:将纪元转换为TIMESTAMP [英] BigQuery: convert epoch to TIMESTAMP

问题描述

我正在尝试将两个表进行范围联接

I'm trying to range-join two tables, like so

SELECT *
FROM main_table h
INNER JOIN
    test.delay_pairs d
ON
    d.interval_start_time_utc < h.visitStartTime
    AND h.visitStartTime < d.interval_end_time_utc

其中 h.visitStartTime 是一个 INT64 时期，并且 d.interval_start_time_utc 和 d.interval_end_time_utc 是正确的 TIMESTAMP s.

where h.visitStartTime is an INT64 epoch and d.interval_start_time_utc and d.interval_end_time_utc are proper TIMESTAMPs.

以上操作失败

No matching signature for operator < for argument types: TIMESTAMP, INT64. Supported signature: ANY < ANY

在 TIMESTAMP()中包装 h.visitStartTime 或 CAST(d.interval_start_time_utc AS INT64)均无效.如何在BigQuery的标准SQL方言中使两者具有可比性?

Neither wrapping h.visitStartTime in TIMESTAMP() nor CAST(d.interval_start_time_utc AS INT64) work. How do I make the two comparable in BigQuery's Standard SQL dialect?

推荐答案

您可以使用

You can use timestamp conversion functions like TIMESTAMP_SECONDS, TIMESTAMP_MILLIS, TIMESTAMP_MICROS

例如，假设您的h.visitStartTime是距unix纪元以来的微秒数

for example, assuming your h.visitStartTime is microseconds since the unix epoch

SELECT *
FROM main_table h
INNER JOIN test.delay_pairs d
ON d.interval_start_time_utc < TIMESTAMP_MICROS(h.visitStartTime)
AND TIMESTAMP_MICROS(h.visitStartTime) < d.interval_end_time_utc  

这篇关于BigQuery:将纪元转换为TIMESTAMP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！