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:
#standardSQLselect 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 Totalfrom `searchs.searchs2016626`GROUP BY DIA)Please, help me. :(
Best Answer
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'))In BigQuery Standard SQL, you can use the EXTRACT timestamp function in order to return an INT64 value corresponding to the part of the timestamp that you want to retrieve, like.
The available parts includes a full list that you can check in the documentation page linked, but in your use case you can directly refer to the HOUR operator in order to retrieve the INT64 representation of the hour value in a field of TIMESTAMP type.
#standardSQL# Create a tableWITH table AS (SELECT TIMESTAMP("2018-07-09T02:40:23.652Z") time)# Extract values from a Timestamp expressionSELECTEXTRACT(DAY FROM time) as day,EXTRACT(MONTH FROM time) as month,EXTRACT(YEAR FROM time) as year,EXTRACT(HOUR FROM time) AS hour,EXTRACT(MINUTE FROM time) as minute,EXTRACT(SECOND from time) as secondFROMtable