CODEWITHSUNDEEP
sqlgoogle-bigquery
Zia J
Zia J

Reputation: 409

Calculating number of days between two timestamp in different formats in BQ

I am new to BQ and struggling with the below: I want to find out no. of days between two timestamp entered in different format. ex: Date_Col1 is 2015/04/13 12:40:44.000 and Date_Col2 is entered as 4/30/2015 17:35 I tried changing format using date(timestamp(4/30/2015 17:35)) i get null everytime. BQ doesn't let me change format of date of Col2 works well with col1. Another issue is Date_Col2 is entered with both single and double digits for month value so cant use concat or substring either. Also sometimes col2 is entered as null. i guess nulls can be replaced with 0.

I was wondering if any one has worked on this use case.

Below is the example of calculating business days between two dates in different formats. It works for other dates but not for Vitals Date(Date_col2 with different format)

(DATEDIFF(TIMESTAMP(hp.ARRIVAL_TIME_PAC_TZ), TIMESTAMP(Vitals_date)) + 1) -(INTEGER((DATEDIFF(TIMESTAMP(hp.ARRIVAL_TIME_PAC_TZ), (TIMESTAMP(Vitals_date))) + 1) / 7) * 2) -(CASE WHEN DAYOFWEEK(TIMESTAMP(Vitals_date)) = 1 THEN 1 ELSE 0 END) -(CASE WHEN DAYOFWEEK(TIMESTAMP(hp.ARRIVAL_TIME_PAC_TZ)) = 7 THEN 1 ELSE 0 END) as AGING_GUTS_ARRIVAL_Vitals_date,

Upvotes: 1

Views: 541

Answers (1)

Mosha Pasumansky
Mosha Pasumansky

Reputation: 14014

To convert col2 into timestamp, you could use the following:

timestamp(concat(
  regexp_extract(col2, r"\d+/\d+/(\d+)"), "/", 
  regexp_extract(col2, r"(\d+)/\d+/\d+"), "/", 
  regexp_extract(col2, r"\d+/(\d+)/\d+"), 
  regexp_extract(col2, r"\d+/\d+/\d+(.*)"), ":00"))

It should work with 1 or 2 digits for month

Upvotes: 1

Related Questions