I have a table on BigQuery with one of the columns being "timestamp". This column is of datatype INT64. I want to add a new column based on that column with the exact dates.
我有一个关于BigQuery的表，其中一列是“timestamp”。此列的数据类型为INT64。我想基于该列添加一个具有确切日期的新列。
The data in the timestamp column is as follows: -600 represents 19:00 EDT on Sunday May 1, 2011 -It is in microseconds, for e.g In one record there is 2506199602819 as the timestamp, this should be around 29 days after.
时间戳列中的数据如下：-600表示2011年5月1日星期日美国东部时间19:00 - 这是微秒，例如在一条记录中有2506199602819作为时间戳，这应该是大约29天后。
What would be the right way to proceed with this? I'm having this table on BigQuery but any SQL would be helpful.
什么是正确的方法来继续这个？我在BigQuery上有这个表，但任何SQL都会有所帮助。
1 个解决方案
#1
3
You can do:
你可以做：
select timestamp_add(timestamp('2011-05-01T19:00:00', 'America/New_York'), interval 2506199602819 - 600 microsecond)
#1
3
You can do:
你可以做：
select timestamp_add(timestamp('2011-05-01T19:00:00', 'America/New_York'), interval 2506199602819 - 600 microsecond)