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)
VALUES
(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.
SELECT
view_id,
DATETIME_SUB(view_datetime, INTERVAL 5 HOUR) AS view_datetime_est, -- Adjusting UTC to EST (UTC-5)
country,
view_count
FROM freshers_in_country_views;
Output
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
