Timezone in BigQuery

[Confuz]

I've encountered an issue where some of my datetime fields are stored in the UTC+2 timezone, while others are in the UTC timestamp format in BigQuery, resulting in a 2-hour offset. Is there a way to set it up so that timestamp fields are displayed in UTC+2 by default?

[ms4446 · Accepted solution]

BigQuery stores all TIMESTAMP values in UTC internally, so there is no direct way to change a session-wide default to UTC+2.

However, you can achieve the desired behavior in your queries using the following methods:

1. FORMAT_TIMESTAMP() Function (Per Query):

• This is the recommended method to display TIMESTAMP data in a specific time zone.
• It formats the TIMESTAMP according to the desired time zone without altering the underlying stored value.

SELECT FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S %Z", your_timestamp_column, "UTC+2") AS time_in_utc_plus_2
FROM your_table;

2. TIMESTAMP Functions for Conversion:

• You can use functions like TIMESTAMP_SECONDS() or TIMESTAMP_MILLIS() to extract the epoch value, then construct a new TIMESTAMP value in the desired time zone.

SELECT TIMESTAMP_SECONDS(UNIX_SECONDS(your_timestamp_column) + 2 * 60 * 60, "UTC+2")
     AS time_in_utc_plus_2
FROM your_table; 

Important Notes:

• TIMESTAMP_ADD(): While TIMESTAMP_ADD() can be used to add a specific interval, it might not be the most accurate method for time zone conversions as it doesn't account for Daylight Saving Time and other time zone nuances. Use the methods above for more robust conversions.

• Data Origin & Consistency:

  • Understand how the timestamps were originally stored (whether in UTC or another time zone) before applying conversions.
  • Maintain consistency in conversions to avoid incorrect results.

• Best Practice: To avoid timezone complexities, it's generally recommended to store all TIMESTAMP data in BigQuery in UTC and perform conversions to desired time zones only during display or analysis.

• Views: To simplify repeated conversions, consider creating views that include the necessary formatting or TIMESTAMP function calculations:

CREATE VIEW your_table_utc_plus_2 AS
SELECT 
  your_timestamp_column,
  FORMAT_TIMESTAMP("%Y-%m-%d %H:%M:%S %Z", your_timestamp_column, "UTC+2") 
    AS time_in_utc_plus_2
FROM your_table;