CODEWITHSUNDEEP
sqlgoogle-analyticsgoogle-bigquery
Pascal Reder
Pascal Reder

Reputation: 25

Combine date, hits.hour & hits.minute field

I would like to combine the date, hits.hour & hits.minute field into one date-field that can easily be read by Tableau for visualization.

At the moment the date is a string and the hits.hour & hits.minute are integers.

In the end the date should be something like YYYY-MM-DD & hh:mm.

Atm I converted the date to a string but I dont know how to combine the fields now, or in general if this first step was correct.

Best, Pascal

SELECT 
fullVisitorId,
visitId,
date,
CAST (hits.hour as string) AS hour,
CAST (hits.minute as string) AS minute,
totals.transactions ,
hits.item.productName,
FROM [X.ga_sessions_20180221] 
WHERE hits.hitNumber =1
GROUP BY 
  fullVisitorId, visitId, date, hour, minute, hits.item.productName, totals.transactions

Upvotes: 1

Views: 1150

Answers (1)

Muhammad Vakili
Muhammad Vakili

Reputation: 708

You just need to concatenate selected values

SELECT 
    fullVisitorId,
    visitId,
    CAST(date as string) +' & ' + CAST (hits.hour as string) + ':' + CAST (hits.minute as string) AS datetime,
    totals.transactions ,
    hits.item.productName,
FROM [X.ga_sessions_20180221] 
WHERE 
    hits.hitNumber =1
GROUP BY 
  fullVisitorId, visitId, date, hour, minute, hits.item.productName, totals.transactions

Upvotes: 1

Related Questions