How Can I Extract Date From Epoch Time In Bigquery Sql

October 26, 2023 Post a Comment

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' )

Solution 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:

SELECTTIMESTAMP(1424184621000000)         # 2015-02-1714:50:21 UTC    
SELECT TIMESTAMP_MICROS(1230219000000000)  # 2008-12-2515:30:00 UTC
SELECT TIMESTAMP_MILLIS(1230219000000)     # 2008-12-2515:30:00 UTC
SELECTDATE(TIMESTAMP(1424184621000000))   # 2015-02-17SELECTDATE(TIMESTAMP('2015-02-17'))       # 2015-02-17SELECTINTEGER(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)
  -(CASEWHEN DAYOFWEEK(TIMESTAMP('2015-06-01')) =1THEN1ELSE0END)
  -(CASEWHEN DAYOFWEEK(TIMESTAMP('2015-06-20')) =7THEN1ELSE0END)

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 UTCSELECT TIMESTAMP_MILLIS(1230219000000)     -- 2008-12-25 15:30:00 UTCSELECT TIMESTAMP_MICROS(1230219000000000)  -- 2008-12-25 15:30:00 UTCSELECTDATE(TIMESTAMP_SECONDS(1230219000)) -- 2008-12-25SELECTCAST('2008-12-25'ASDATE)          -- 2008-12-25SELECTDATE('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 FROMDATE('2015-06-01'))=1,1,0)
  - IF(EXTRACT(DAYOFWEEK FROMDATE('2015-06-20'))=7,1,0)

Solution 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

Alternatively TIMESTAMP_SECONDS(visitStartTime) for seconds, e.g. in Google Analytics.

Solution 3:

If you have the Legacy SQL option, to answer question 1, given a column of UNIX epoch time 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 a UNIX timestamp in microseconds to a TIMESTAMP data type.
Example:
SELECT USEC_TO_TIMESTAMP(1349053323000000);

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

Spark Sql Blog