How can I extract date from epoch time in BigQuery SQL

Question

1. 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.

   date_add( (timestamp( Hp.ASSIGN_TIME)), 1970-01-01,"second" ) as Extracted_date_Epoch 

   Ex time format(1424184621000000)

2. 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 

Answer 1

BigQuery offers two SQL modes. The original answer was based on Legacy Mode, but then I decided to update the answer by adding Standard Mode alternatives.

Legacy Mode

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

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:

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 TIMESTAMP and 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)