如何从 BigQuery SQL 中的纪元时间中提取日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/30684920/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I extract date from epoch time in BigQuery SQL
提问by Zia J
I have date stored in
Epoch Timeand I want to extractDatefrom it. I tried the code below and I getnullas output.date_add( (timestamp( Hp.ASSIGN_TIME)), 1970-01-01,"second" ) as Extracted_date_EpochEx time format(1424184621000000)
One more question. The code below give me days correctly but not business days, it gives all days, is it possible to get just business days betweeen two times stored in Epoch time?
INTEGER(((Hp.ASSIGN_TIME - Hp.ARRIVAL_TIME) / 1000000) / 86400) as Days
我存储了日期
Epoch Time，我想从中提取Date。我尝试了下面的代码，我得到null了输出。date_add( (timestamp( Hp.ASSIGN_TIME)), 1970-01-01,"second" ) as Extracted_date_EpochEx时间格式(1424184621000000)
还有一个问题。下面的代码给了我正确的天数而不是工作日，它给出了所有的日子，是否有可能在大纪元时间存储两次之间的工作日？
INTEGER(((Hp.ASSIGN_TIME - Hp.ARRIVAL_TIME) / 1000000) / 86400) as Days
回答by Qorbani
BigQuery offers two SQL modes. The original answer was based on Legacy Mode, but then I decided to update the answer by adding Standard Modealternatives.
BigQuery 提供两种 SQL 模式。原始答案基于Legacy Mode，但后来我决定通过添加标准模式替代方案来更新答案。
Legacy Mode
传统模式
To convert timestampto dateyou can use BigQuery date/time functions:
要转换timestamp为date您可以使用BigQuery 日期/时间函数：
SELECT TIMESTAMP(1424184621000000) # 2015-02-17 14:50:21 UTC
SELECT TIMESTAMP_MICROS(1230219000000000) # 2008-12-25 15:30:00 UTC
SELECT TIMESTAMP_MILLIS(1230219000000) # 2008-12-25 15:30:00 UTC
SELECT DATE(TIMESTAMP(1424184621000000)) # 2015-02-17
SELECT DATE(TIMESTAMP('2015-02-17')) # 2015-02-17
SELECT INTEGER(TIMESTAMP('2015-02-17')) # 1424131200000000
To calculate number of days between two dates (For example between 6/1/15 to 6/20/15), you can do this:
要计算两个日期之间的天数（例如 6/1/15 到 6/20/15），您可以执行以下操作：
SELECT (DATEDIFF(TIMESTAMP('2015-06-20'), TIMESTAMP('2015-06-01')) + 1)
And finally to calculate business days, you can use following:
最后要计算工作日，您可以使用以下内容：
SELECT
(DATEDIFF(TIMESTAMP('2015-06-20'), TIMESTAMP('2015-06-01')) + 1)
-(INTEGER((DATEDIFF(TIMESTAMP('2015-06-20'), TIMESTAMP('2015-06-01')) + 1) / 7) * 2)
-(CASE WHEN DAYOFWEEK(TIMESTAMP('2015-06-01')) = 1 THEN 1 ELSE 0 END)
-(CASE WHEN DAYOFWEEK(TIMESTAMP('2015-06-20')) = 7 THEN 1 ELSE 0 END)
This is simple business days calculation with considering Sat and Sun as weekends and not involving any holidays.
这是简单的工作日计算，将周六和周日视为周末，不涉及任何假期。
Standard Mode
标准模式
Here are some sample functions you can use for dealing with TIMESTAMPand DATE.
以下是一些可用于处理TIMESTAMP和 的示例函数DATE。
SELECT TIMESTAMP_SECONDS(1230219000) -- 2008-12-25 15:30:00 UTC
SELECT TIMESTAMP_MILLIS(1230219000000) -- 2008-12-25 15:30:00 UTC
SELECT TIMESTAMP_MICROS(1230219000000000) -- 2008-12-25 15:30:00 UTC
SELECT DATE(TIMESTAMP_SECONDS(1230219000)) -- 2008-12-25
SELECT CAST('2008-12-25' AS DATE) -- 2008-12-25
SELECT DATE('2008-12-25', 'UTC') -- 2008-12-25
For calculating days between two dates:
计算两个日期之间的天数：
SELECT DATE_DIFF(DATE('2015-06-20'), DATE('2015-06-01'), DAY)
And finally calculate simple business days like above:
最后像上面一样计算简单的工作日：
SELECT
DATE_DIFF(DATE('2015-06-20'), DATE('2015-06-01'), DAY)
- DIV(DATE_DIFF(DATE('2015-06-20'), DATE('2015-06-01'), DAY),7)*2
- IF(EXTRACT(DAYOFWEEK FROM DATE('2015-06-01'))=1,1,0)
- IF(EXTRACT(DAYOFWEEK FROM DATE('2015-06-20'))=7,1,0)
回答by Krishnaa
If you are using standardSQL dialect in BigQuery, this function does the conversion to human readable timestamp TIMESTAMP_MICROS(1424184621000000) --> 2015-02-17 14:50:21 UTC. Ref: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#timestamp-string
如果您在 BigQuery 中使用标准 SQL 方言，此函数会转换为人类可读的时间戳 TIMESTAMP_MICROS(1424184621000000) --> 2015-02-17 14:50:21 UTC。参考：https: //cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#timestamp-string
Alternatively TIMESTAMP_SECONDS(visitStartTime)for seconds, e.g. in Google Analytics.
或者TIMESTAMP_SECONDS(visitStartTime)几秒钟，例如在谷歌分析中。
回答by Kirby
If you have the Legacy SQL option, to answer question 1, given a column of UNIX epoch time in milliseconds, like 1524375336000,
如果您有 Legacy SQL 选项，要回答问题 1，给出一列以毫秒为单位的 UNIX 纪元时间，例如 1524375336000，
I used
SELECT USEC_TO_TIMESTAMP(Hp.ASSIGN_TIME * 1000) AS the_date FROM table;
我用了
SELECT USEC_TO_TIMESTAMP(Hp.ASSIGN_TIME * 1000) AS the_date FROM table;
╔═══╦═══════════════╦═════════════════════════════╗
║ ║ ASSIGN_TIME ║ the_date ║
╠═══╬═══════════════╬═════════════════════════════╣
║ 1 ║ 1524375336000 ║ 2018-04-22 05:35:36.000 UTC ║
╚═══╩═══════════════╩═════════════════════════════╝
USEC_TO_TIMESTAMP(<expr>)Converts a UNIX timestamp in microseconds to a TIMESTAMP data type.Example:
SELECT USEC_TO_TIMESTAMP(1349053323000000);
USEC_TO_TIMESTAMP(<expr>)将 UNIX 时间戳（以微秒为单位）转换为 TIMESTAMP 数据类型。示例：
SELECT USEC_TO_TIMESTAMP(1349053323000000);
https://cloud.google.com/bigquery/docs/reference/legacy-sql#usec_to_timestamp
https://cloud.google.com/bigquery/docs/reference/legacy-sql#usec_to_timestamp
