sql - 时间戳到日期 SQL

Question

我需要像这样更改时间戳:

1519152103659000     
1519152113786000     
1519152118754001     
1519152118972002     
1519152119026003

到 sql 中的日期。这个时间戳是我表中的一列。我使用时间戳命令但出现错误:

错误:参数类型为 INT64 的函数 TIMESTAMP 没有匹配的签名。支持的签名:TIMESTAMP(STRING, [STRING]);时间戳(日期，[字符串])； TIMESTAMP(DATETIME, [STRING]) 在 [2:1]

谢谢!!

Answer 1

以下示例适用于 BigQuery StandardSQL

#standardSQL
WITH t AS (
  SELECT 1519152103659000 AS ts UNION ALL
  SELECT 1519152113786000 UNION ALL
  SELECT 1519152118754001 UNION ALL
  SELECT 1519152118972002 UNION ALL
  SELECT 1519152119026003 
)
SELECT 
  ts AS timestamp_in_microseconds_as_int64,  
  TIMESTAMP_MICROS(ts) AS timestamp_as_timestamp,
  DATE(TIMESTAMP_MICROS(ts)) AS dt
FROM t  

结果为

Row timestamp_in_microseconds_as_int64  timestamp_as_timestamp          dt   
1   1519152103659000                    2018-02-20 18:41:43.659 UTC     2018-02-20   
2   1519152113786000                    2018-02-20 18:41:53.786 UTC     2018-02-20   
3   1519152118754001                    2018-02-20 18:41:58.754 UTC     2018-02-20   
4   1519152118972002                    2018-02-20 18:41:58.972 UTC     2018-02-20   
5   1519152119026003                    2018-02-20 18:41:59.026 UTC     2018-02-20