LAG（）和时间戳 [英] LAG() and timestamps

问题描述

可能的BigQuery bug在这里？我正在运行使用LAG（）函数来获取以前的时间戳值的查询。示例：

架构：

  id：STRING 
 time：TIMESTAMP 
  

查询：

<$ p $ （以id为单位，按时间顺序排列）as previous_time
FROM dataset.table
c $ SELECT $ id
time as current_time，LAG（time，1） / code>

当current_time作为时间戳值返回时，previous_time返回为整数值。例如：

 12345，2014-04-09 00:19:01 UTC，1396992237000000 
  

关于如何获得LAG（）的任何想法都会返回一个TIMESTAMP？

解决方案

是的，这确实是一个错误（我在内部提交）。这是泄漏时间戳的内部表示形式，以微秒为单位。

您可以通过使用 USEC_TO_TIMESTAMP（）函数。如：

  SELECT id，
时间为current_time，
 USEC_TO_TIMESTAMP（LAG（time，1） OVER（PARTITION BY ID，按时间排序））as previous_time 
 FROM dataset.table 
  

也就是说，一旦我们修复了这个错误，这将会中断。如果您想在修复问题时收到通知，可以在BigQuery公开问题跟踪器此处。

Possible BigQuery bug here? I'm running a query that uses the LAG() function to get the previous timesamp value. Example:

schema:

id: STRING
time: TIMESTAMP

query:

SELECT id, 
time as current_time, LAG(time,1) OVER (PARTITION BY id, order by time) as previous_time
FROM dataset.table

While "current_time" comes back as a timestamp value, "previous_time" comes back as an integer value. e.g.:

"12345", "2014-04-09 00:19:01 UTC", 1396992237000000

Any ideas on how to get LAG() return a TIMESTAMP?

解决方案

Yes, this is indeed a bug (I've filed it internally). This is leaking the internal representation of the timestamp, which is in microseconds.

You can work around this by using the USEC_TO_TIMESTAMP() function. As in:

SELECT id, 
time as current_time, 
USEC_TO_TIMESTAMP(LAG(time,1) OVER (PARTITION BY id, order by time)) as previous_time
FROM dataset.table

That said, this will break once we fix the bug. If you want to be notified when it is fixed, you can file it at the BigQuery public issue tracker here.

这篇关于LAG（）和时间戳的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！