CODEWITHSUNDEEP
google-bigqueryextractlegacy-sql
desky
desky

Reputation: 13

Extracting hour by using coalese in SQL on a timestamp

I am trying to update a query to extract the hour from a timestamp and I keep getting an error. The error I get is due to the FROM clause I was using. 

SELECT 
analytics_platform_data_type
, activity_date_pt
, activity_timestamp_pt
, analytics_platform_timestamp_utc
, analytics_platform_timestamp_utc_iso

--This is the clause that is causing the problem (Begin)
, extract(hour from coalesce(activity_timestamp_pt)) as latd_hour_pt
--Clause above is the issue; Line above is line 9 (End)

, analytics_platform_ platform
, ad_channel_name
, publisher_name
, ip_address
, analytics_platform_unique_activity_id
, click_id
, latd_custom_fields
FROM table_date_range([AllData_AnalyticsMobileData_], timestamp('2018-09- 
25'), timestamp('2018-09-27')) 
where 1=1
and analytics_platform_data_type = 'CLICK'
and partner_name = 'ABC123'

If I remove the extract hour piece the query works fine. When I add it I get the error: Encountered " "FROM" "from "" at line 9, column 16. Was expecting: ")" ...

I have seen the clause I am trying to use in the above query used before, but it was a much more complex query that was using sub queries. Really not sure what the issue is. (Using Google Big Query Legacy SQL)

Upvotes: 0

Views: 118

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172984

Your query is mixing Legacy Syntax (table_date_range) with Standard Syntax (Extract)

If for some reason you need to stick with Legacy SQL - use HOUR() instead of EXTRACT()

But it is much recommended to migrate stuff to Standard SQL - where you should use wildcard functions instead of table_date_range

Something like 

FROM `project.dataset.AllData_AnalyticsMobileData_*`
WHERE _TABLE_SUFFIX BETWEEN '2018-09-25' AND '2018-09-27'

see more at https://cloud.google.com/bigquery/docs/reference/standard-sql/migrating-from-legacy-sql#table_decorators_and_wildcard_functions in Migrating to Standard SQL doc

Upvotes: 1

Related Questions