The behavior you're observing is due to the way BigQuery handles timestamps. In BigQuery, a TIMESTAMP data type represents an absolute point in time, and it is always displayed in UTC when queried, regardless of any timezone conversions you apply.
When you use the TIMESTAMP(DATETIME(timestamp, timezone)) function, you are effectively converting the timestamp to a different timezone, but the result is still a TIMESTAMP value, which BigQuery displays in UTC.
Here's a breakdown of your query:
- CURRENT_TIMESTAMP() returns the current timestamp in UTC.
- TIMESTAMP(DATETIME(CURRENT_TIMESTAMP(), "America/Los_Angeles")) converts the current UTC timestamp to the "America/Los_Angeles" timezone, but the result is still a TIMESTAMP and is displayed in UTC.
To see the time in the Pacific Standard Time (PST) timezone, you would need to use a STRING representation of the datetime, or use a DATETIME type which retains the timezone conversion but does not store timezone information. For example:
SELECT CURRENT_TIMESTAMP() AS current_timestamp_utc, DATETIME(CURRENT_TIMESTAMP(), "America/Los_Angeles") as current_timestamp_pst
This query will show current_timestamp_pst in PST, but as a DATETIME value, not as a TIMESTAMP. Remember, DATETIME does not store timezone information, but it will display the converted time correctly.
LinkedIn
Twitter
