How can I extract just the hour of a timestamp using standardSQL

Question

How can I extract just the hour of a timestamp using standardSQL.

I've tried everything and no function works. The problem is that I have to extract the time from a column and this column is in the following format:2018-07-09T02:40:23.652Z

If I just put the date, it works, but if I put the column it gives the error below:

Syntax error: Expected ")" but got identifier "searchIntention" at [4:32]

Follow the query below:

#standardSQL
select TOTAL, dia, hora FROM 
(SELECT cast(replace(replace(searchIntention.createdDate,'T',' '),'Z','')as 
DateTime) AS DIA, 
FORMAT_DATETIME("%k", DATETIME searchIntention.createdDate) as HORA,
count(searchintention.id) as Total
from `searchs.searchs2016626`
GROUP BY DIA)

Please, help me. :(

Answer 1

How can I extract just the hour of a timestamp using standardSQL?

Below is for BigQuery Standard SQL

You can use EXTRACT(HOUR FROM yourTimeStampColumn)

for example:

SELECT EXTRACT(HOUR FROM CURRENT_TIMESTAMP())   

or

SELECT EXTRACT(HOUR FROM TIMESTAMP '2018-07-09T02:40:23.652Z')

or

SELECT EXTRACT(HOUR FROM TIMESTAMP('2018-07-09T02:40:23.652Z'))