How to convert visit start time to a readable human date or timestamp in Big Query?

Question

I'm trying to convert the visit start time variable which is captured in big query to a readable date or preferably timestamp.

I'm using standard SQL.

I've tried using sec_to_timestamp but I believe this only works in legacy SQL within BQ.

The google documentation talks about converting to timestamp from date expression and string expression however the visit start time is an integer.

https://cloud.google.com/bigquery/docs/reference/standard-sql/functions-and-operators

I've looked at the following link and tried the code in this but I get the error "function not found: integer"

Big Query - Google Analytics - Time diff between first visit and purchase

Any other advice on dates/times captured by BQ would be greatly received.

Thank you

Solution 1

Assuming that your start time is in seconds relative to the Unix epoch, you can use TIMESTAMP_SECONDS to convert it to a timestamp. For example:

#standardSQL
SELECT
  TIMESTAMP_SECONDS(start_time_sec) AS timestamp
FROM (
  SELECT 1488303123 AS start_time_sec
);

If you want to convert the timestamp to a date, you can use EXTRACT with an optional timezone:

#standardSQL
SELECT
  EXTRACT(DATE FROM TIMESTAMP_SECONDS(start_time_sec)
          AT TIME ZONE 'America/Los_Angeles') AS date
FROM (
  SELECT 1488303123 AS start_time_sec
);

Solution 2

I was looking for the most resent visitStartTime in the ga_realtime_sessions table in BiqQuery. I parsed the timestamps as follows:

SELECT
  visitStartTime, 
  TIMESTAMP_SECONDS(visitStartTime) as starttime_UTC,
  DATETIME(TIMESTAMP_SECONDS(visitStartTime), "Europe/Amsterdam") as starttime_LOCAL,
FROM `ga_sessions_*`
ORDER BY visitStartTime desc 
LIMIT 30

Resulting in records like:

Row visitStartTime  starttime_UTC             starttime_LOCAL   
1   1562153975      2019-07-03 11:39:35 UTC   2019-07-03T13:39:35