Does anyone know how to convert Google Analytics "Date Hour and Minute"?

Canioagain · 2022-02-11T16:18:56+00:00

There was an error rendering this rich post.

Does anyone have a solution they are using in a Magic ETL to easily convert Google Analytic's Date Hour and Minute output into something resembling an actual date time?

Find more posts tagged with

Date

Magic ETL

Accepted answers

GrantSmith

You can use a formula tile to splice out the different parts and assemble it as a timestamp:

TIMESTAMP(CONCAT (
  LEFT(`Date Hour and Minute`, 4), '-',
  SUBSTRING(`Date Hour and Minute`, 5, 2), '-',
  SUBSTRING(`Date Hour and Minute`, 7, 2), ' ',
  SUBSTRING(`Date Hour and Minute`, 9, 2), ':',
  SUBSTRING(`Date Hour and Minute`, 1, 2)))

All comments

GrantSmith

You can use a formula tile to splice out the different parts and assemble it as a timestamp:

TIMESTAMP(CONCAT (
  LEFT(`Date Hour and Minute`, 4), '-',
  SUBSTRING(`Date Hour and Minute`, 5, 2), '-',
  SUBSTRING(`Date Hour and Minute`, 7, 2), ' ',
  SUBSTRING(`Date Hour and Minute`, 9, 2), ':',
  SUBSTRING(`Date Hour and Minute`, 1, 2)))

Canioagain

@GrantSmith Awesome!