如何在BigQuery SQL中从纪元时间中提取日期

I have date stored in Epoch Time and I want to extract Date from it. I tried the code below and I get null as output.

我有日期存储在*时间，我想从中提取日期。我尝试了下面的代码，我得到null作为输出。
```
date_add( (timestamp( Hp.ASSIGN_TIME)), 1970-01-01,"second" ) as Extracted_date_Epoch
```
Ex time format(1424184621000000)

出行时间格式（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
```

3 个解决方案

#1

To convert timestamp to date you can use BigQuery date/time functions:

要将时间戳转换为日期，您可以使用BigQuery日期/时间函数：

SELECT TIMESTAMP(1424184621000000)       # 2015-02-17 14:50:21 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:

要计算两个日期之间的天数（例如，在2015年6月1日至2015年6月20日之间），您可以执行以下操作：

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.

这是简单的工作日计算，将周六和周日作为周末考虑，不涉及任何假期。

#2

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

#3

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）作为the_date FROM表;

╔═══╦═══════════════╦═════════════════════════════╗
║   ║ 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.

USEC_TO_TIMESTAMP（）将UNIX时间戳（以微秒为单位）转换为TIMESTAMP数据类型。

Example:

例：

SELECT USEC_TO_TIMESTAMP(1349053323000000);

SELECT USEC_TO_TIMESTAMP（1349053323000000）;

https://cloud.google.com/bigquery/docs/reference/legacy-sql#usec_to_timestamp

#1