Google Big Query and Google Analytics - convert INTEGER to TIME

Question

I have a problem with data from Google Analytics 4. I have a total time duration metric and its in BQ but as integer. I need to change it TIME since im using the data in PowerBi. Any ideas?

I tried all the solutions in the internet - none of them is working. I tried to transform the data in PowerBi - but it didn't work.

Answer 1

Your question is not clear as to what the value of integer is. It could be

1. integer representation of date as 20240117 -(2024 year 01 month 17 date)
2. integer representation of time as 113000 -( 11 PM 30 min 00 sec)
3. integer representation of timestamp as 20240117010110 (Y,M,D,H,M,S)
4. It could be the number of minutes/seconds as a quantity.

Assuming case 1: Use PARSE_DATE with following parse string indicating %Y year, %m month, %d date

select PARSE_DATE('%Y%m%d', cast(20240117 as string))

Result: 2024-01-17

Assuming case 2: Use PARSE_TIME with following parse string indicating %I Hours %M Minutes, %S seconds

select PARSE_TIME('%I%M%S', cast(113000 as string))

Result: 11:30:00

Case 3: Use

select PARSE_TIMESTAMP('%Y%m%d%I%M%S', cast(20240117010110 as string))

Result: 2024-01-17 01:01:10 UTC

Case 4: You need to know from when the time is being counted. and convert that seconds/minutes into days and add to that base date.

Find Documentation here PARSE_TIMESTAMP , PARSE_DATE, PARSE_TIME