In data analysis, sometimes you may need to perform arithmetic on date columns, such as subtracting a number of days to obtain a past date. This guide will walk you through the process of subtracting days from a given date in Google BigQuery. This tutorial provides a clear understanding of date arithmetic in BigQuery, focusing on subtracting days.
Step 1: Setting up the table in BigQuery
We’ll set up a table named freshers_in_country_views that captures view counts by country on specific dates.
CREATE TABLE freshers_in_country_views (
view_id INT64,
view_date DATE,
country STRING,
view_count INT64
);
Step 2: Inserting sample data
For this example, let’s insert some sample data with various dates.
INSERT INTO freshers_in_country_views (view_id, view_date, country, view_count)
VALUES
(1, DATE "2023-09-14", "USA", 1500),
(2, DATE "2023-09-13", "India", 2100),
(3, DATE "2023-09-12", "UK", 1300);
Step 3: Subtracting days from the date column
Assuming we want to find out the date 7 days before the recorded view_date, we can use the DATE_SUB function.
SELECT
view_id,
view_date,
DATE_SUB(view_date, INTERVAL 7 DAY) AS date_seven_days_prior,
country,
view_count
FROM freshers_in_country_views;
Output
view_id | view_date | date_seven_days_prior | country | view_count
------- | ----------- | --------------------- | ------- | ----------
1 | 2023-09-14 | 2023-09-07 | USA | 1500
2 | 2023-09-13 | 2023-09-06 | India | 2100
3 | 2023-09-12 | 2023-09-05 | UK | 1300
BigQuery import urls to refer
