CODEWITHSUNDEEP
sqlgoogle-bigquery
JY2k
JY2k

Reputation: 2909

Converting hour extraction to Bigquery standardSQL

I am converting query from Snowflake to Bigquery: 

date_part(hour, convert_timezone('UTC', 'US/Eastern', to_timestamp(to_numeric((FLOOR(start_ts)))))) in (6,7,8,9)

start_ts is of the format: 1563026408.17193 and is a float.

Upvotes: 0

Views: 108

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Equivalent expression for BigQuery Standard SQL will be 

EXTRACT(HOUR FROM DATETIME(TIMESTAMP_MICROS(CAST(1000000 * start_ts AS INT64)), 'US/Eastern')) IN (6,7,8,9)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can use TIMESTAMP_MILLIS() to convert the value to a timestamp. Then convert to a DATETIME:

select DATETIME(timestamp_MICROS(cast(1563026408.17193 * 1000000 as int64)), 'America/New_York')

Instead of datepart(), use extract(hour . . .) to extract the hour.

Upvotes: 1

Related Questions