Reputation: 93
Could you please help me on this query: I don't understand why the TIMESTAMP_MILLIS is not working
SELECT DATE,
MAX(CASE WHEN customDimensions.index = 1 THEN customDimensions.value END) AS CUSTOMDIMENSIONS_VALUE,
visitNumber,
fullvisitorid,
device.deviceCategory,
hits.page.pagePath,
IFNULL(hits.page.pagePathLevel2, '') AS HITS_PAGE_PAGEPATHLEVEL2,
IFNULL(hits.page.pagePathLevel3, '') AS HITS_PAGE_PAGEPATHLEVEL3,
hits.page.pageTitle,
TIMESTAMP_SECONDS(visitStartTime) AS visitStart,
TIMESTAMP_MILLIS(1000 * visitStartTime + hits.time) AS hitStart,
hits.hitNumber,
hits.hour,
hits.minute,
hits.isEntrance,
hits.isExit,
CONCAT(fullvisitorid, STRING(visitid)) AS sessionid
FROM (FLATTEN([Table], customDimensions.value ))
WHERE hits.page.hostname CONTAINS 'X'' AND customDimensions.value != "null" AND hits.type = 'PAGE' AND (customDimensions.index = 1 OR hits.customDimensions.index = 11)
GROUP EACH BY DATE, visitStartTime, fullVisitorId, visitNumber, device.deviceCategory, hits.page.pagePath, hits.page.pageTitle, hits.hitNumber, hits.time, hits.hour, hits.minute, hits.isEntrance, hits.isExit
LIMIT 100000
Upvotes: 0
Views: 284
Reputation: 173190
I don't understand why the TIMESTAMP_MILLIS is not working. Could you please help me
The problem with your query is that you are using mixture of BigQuery Legacy SQL
and Standard SQL
in the same query.
For example:
Below part of your query - is a Legacy SQL Feature
FROM (FLATTEN([Table], customDimensions.value ))
Whereas TIMESTAMP_MILLIS
and TIMESTAMP_SECONDS
are functions supported by Standard SQL Only - you can find similar functions in Legacy SQL though (for example MSEC_TO_TIMESTAMP
and SEC_TO_TIMESTAMP()
)
You need to stick with one or another. Using Standard SQL
is recommended by BigQuery Team and I definitely second this!
Upvotes: 1