Google BigQuery returns same value for visiId and visitStartTime

Question

I run this very simple query on BigQuery and it returned the visitId value as visit start time. Code is below and you can also see the screenshot here: visitStartTime and visitId same value

SELECT visitStartTime, visitId FROM [83606741.ga_sessions_intraday_20160606] group by visitStartTime, visitId order by visitStartTime DESC;

How can I get the session start time to be shown like 11:45 and what is the reason these two columns have the same value?

Thank you for the replies!

Answer 1

The following function will return the time of the visit.

SELECT time(SEC_TO_TIMESTAMP(visitid)) as visit_time FROM [83606741.ga_sessions_intraday_20160606]

Like you say, visitID is the same as visitstarttime. For this reason visitid is not a unique reference for a session. To create a unique sessionid you need to concatenate it with the visitorid:

concat(fullvisitorid, string(visitid)) as sessionid