Surprising Outcome from BigQuery EXTRACT HOUR Function

Appreciation
Solution 1:
For BigQuery Standard SQL, it is recommended to utilize the ‘you should use’ instead of ‘Instead of.’
Solution 2:
To extract time and specify the timezone, you can utilize the EXTRACT function as follows.
Inquiry:
What is the method to extract the hour from a timestamp/time column in BigQuery?
Sample value of Timestamp Column: 4/1/2020 12:57:55
Sample value of Time Column: 12:57:55
I attempted to use ‘returns only one value’ but it did not address my concern.
For the time column, you can use the following approach:
Solution 2:
To extract the hour from the scalar column, you can employ:
It functions for both ‘s and ‘s.


Question:

I am utilizing the bigquery’s
EXTRACT function
function to retrieve an hour from a TIMESTAMP column, which yields an
UTC time
.

There exists a discrepancy between the anticipated outcome and the actual outcome, stemming from an inconsistency. Typically, there is a time difference of four hours between Coordinated Universal Time (UTC) and New York (NY) time.

Here is the query and the result:

select reservation_time, EXTRACT(HOUR from reservation_time AT TIME ZONE "America/New_York") 
from matview.resview
#
# EXPECTED
#
2021-06-06 02:00:00    22
2021-06-06 02:00:00    22
2021-06-06 02:00:00    22
2021-06-06 01:30:00    21
#
# REAL RESULT
#
2021-06-06 02:00:00    20
2021-06-06 02:00:00    20
2021-06-06 02:00:00    20
2021-06-06 01:30:00    19

Edit:

Observe a different
SQL Query
along with its corresponding result.

select FORMAT_TIMESTAMP("%F %T %Z", reservation_time), reservation_time 
from matview.resview
#
# OUTPUT
#
2021-03-11 14:30:00 UTC 2021-03-11 15:30:00
2021-04-09 13:30:00 UTC 2021-04-09 15:30:00
2020-07-23 20:30:00 UTC 2020-07-23 22:30:00
2020-07-23 21:00:00 UTC 2020-07-23 23:00:00
2020-07-23 20:00:00 UTC 2020-07-23 22:00:00


Solution:

If your default timezone is different, you may not see times in UTC. Instead, use this query to compare
extracted hour
with UTC times:
“””.

select
  FORMAT_TIMESTAMP("%F %T %Z", reservation_time),
  EXTRACT(HOUR from reservation_time AT TIME ZONE "America/New_York")
from matview.resview

Frequently Asked Questions

Posted in Sql