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

Canioagain

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?

?

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

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!