danielsmeyer
3/25/2020 - 4:13 PM

Extract hour from timestamp subtracted by timezone offset

Had to treat fringe case of timezone '-03:-30', so the regexp is replacing the second '-'' and what follows with '00' to standardize the timezone.

hour_timezone_extract
content_copyfile_download
select
	trunc(EXTRACT(hour from (timestamp::timestamp at time zone REGEXP_REPLACE(utc_timestamp_offset,'-(\d{1,3})$', '00' ) ) )) date,
	count(*)
from test_spotify_stream
group by
	trunc(EXTRACT(hour from (timestamp::timestamp at time zone REGEXP_REPLACE(utc_timestamp_offset,'-(\d{1,3})$', '00' ))))