Posted in bigquery

Navigating date arithmetic in Google BigQuery : Subtracting days from a date column

   September 14, 2023  Leave a Comment on Navigating date arithmetic in Google BigQuery : Subtracting days from a date column
Google Big Query @ Freshers.in

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
);
SQL

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);
SQL

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;
SQL
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
Bash

BigQuery import urls to refer

Post Views: 4
Author: user
Website

Leave a Reply