Google BigQuery: Converting Timezones in a datetime column – Sample code inside

Google Big Query @

Mastering timezone conversion in Google BigQuery

In the world of global data analytics, dealing with data across multiple time zones is common. To ensure accuracy and clarity in analysis, one might need to convert DateTime columns from one timezone to another. This article will guide you through the process of timezone conversion in Google BigQuery.

Step 1: Setting up the Table in BigQuery

We will create a table named freshers_in_country_views with a DateTime column.

CREATE TABLE freshers_in_country_views (
    view_id INT64,
    view_datetime DATETIME,
    country STRING,
    view_count INT64

Step 2: Inserting Sample Data

Let’s populate our table with sample data, considering that the view_datetime is in UTC.

Insert Statement:
INSERT INTO freshers_in_country_views (view_id, view_datetime, country, view_count)
(1, DATETIME "2023-09-14 15:43:22", "USA", 1500),
(2, DATETIME "2023-09-14 18:05:45", "India", 2100),
(3, DATETIME "2023-09-14 09:15:00", "UK", 1300);

Step 3: Converting the DateTime Column to a Different Timezone

Suppose we want to convert the view_datetime from UTC to Eastern Standard Time (EST) for analysis. We’ll use BigQuery’s DATETIME_ADD and DATETIME_SUB functions.

    DATETIME_SUB(view_datetime, INTERVAL 5 HOUR) AS view_datetime_est,  -- Adjusting UTC to EST (UTC-5)
FROM freshers_in_country_views;
view_id | view_datetime_est         | country | view_count
------- | ------------------------- | ------- | ----------
1       | 2023-09-14 10:43:22       | USA     | 1500
2       | 2023-09-14 13:05:45       | India   | 2100
3       | 2023-09-14 04:15:00       | UK      | 1300

This method uses a static offset for timezone conversion. For time zones that observe daylight saving time (like EST/EDT), you might want to incorporate a more dynamic method using timezone functions or consider using the TIMESTAMP type instead of DATETIME.

BigQuery import urls to refer

Author: user

Leave a Reply