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)