Search code examples
google-bigqueryhour

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

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. :(

Solution

  • 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'))