How can I extract a date from an era in BigQuery SQL

Question

• I have a 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)

• One more question. The code below gives me the days correctly, but not the working days, it gives me all the days, is it possible to get only working days between the two moments stored in the Epoch era?

   INTEGER(((Hp.ASSIGN_TIME - Hp.ARRIVAL_TIME) / 1000000) / 86400) as Days 

Answer 1

To convert timestamp to date you can use the 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 the number of days between two dates (for example, between 6/1/15 and 6/20/15), you can do this:

 SELECT (DATEDIFF(TIMESTAMP('2015-06-20'), TIMESTAMP('2015-06-01')) + 1) 

And finally, you can use the following to calculate business days:

 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 a simple calculation of working days taking into account Saturdays and Sundays as weekends and without any holidays.

Answer 2

If you use the standard SQL dialect in BigQuery, this function converts to a human-readable timestamp TIMESTAMP_MICROS (1424184621000000) → 2015-02-17 14:50:21 UTC. Link: https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators#timestamp-string

Or TIMESTAMP_SECONDS(visitStartTime) for a few seconds, for example, in Google Analytics.

Answer 3

If you have the Legacy SQL option to answer question 1, given a UNIX-era time column in milliseconds, like 1524375336000,

I used 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 the UNIX timestamp in microseconds to the TIMESTAMP data type.

An example :

SELECT USEC_TO_TIMESTAMP(1349053323000000);

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