CODEWITHSUNDEEP
google-bigquery
sPujade
sPujade

Reputation: 93

BigQuery Issue with time query

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

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

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

Related Questions