Big Query (SQL) Add one month to the date (Issue) - (Data Studio)

Question

Currently, I'm adding an extra month this way:

DATE_ADD(date, INTERVAL 1 MONTH) AS pDate

I'm trying to compare two values by month, by using the same date range. So I made another custom field with date +1 month and when I use it...it missing days with 31.

June 30 +1 month = July 31 and as I using custom field... it missing fields where date with 31

EDITING v1.0

I have a database for a year, each day is presented.

As example:

01012018
....
31012018
01022018
...
28022018

I need to compare two time period and to solve this issue I create a custom field which takes the date and add +1 month, so after in Data studio (could be in any over platform) can compare 25-30January with 25-30February, the issue is when I add 1 month to the date 30012018 it becomes 30022018 (WHich as you know does not exist)

Anyway, I'm sticking with this idea, but maybe there are any other ways of doing this? WIll repeat again, I need to compare the same date but from different month - 15th January -20 January WITH 15th February - 20th February, but again issue where 30th-31st appears

Answer 1

I need to compare two time period ...

Unfortunately, you question still does not show what exactly your use case - so below is attempt to give you an idea based on generalization of what I see in question

In below (for BigQuery Standard SQL):

• the project.dataset.table is your real table with dates and metrics you want to compare.
• days_range and months_range - allow you to set range or days and months respectively without doing any changes in main SELECT statement

---

#standardSQL
WITH days_range AS (
  SELECT 15 start_day, 20 end_day
), months_range AS (
  SELECT 1 start_month, 4 end_month
)
SELECT 
  CONCAT(CAST(MIN(day_date) AS STRING), ' - ', CAST(MAX(day_date) AS STRING)) interval_days,
  SUM(metric) interval_metric
FROM `project.dataset.table`, days_range, months_range
WHERE EXTRACT(DAY FROM day_date) BETWEEN start_day AND end_day
AND EXTRACT(MONTH FROM day_date) BETWEEN start_month AND end_month
GROUP BY DATE_TRUNC(day_date, MONTH)
-- ORDER BY 1    

To play with above you can use below script that mimics your real table by generating days for year of 2018 along with random metrics

#standardSQL
WITH `project.dataset.table` AS (
  SELECT day_date, CAST(100 * RAND() AS INT64) metric
  FROM UNNEST(GENERATE_DATE_ARRAY('2018-01-01', '2018-12-31')) day_date 
), days_range AS (
  SELECT 15 start_day, 20 end_day
), months_range AS (
  SELECT 1 start_month, 4 end_month
)
SELECT 
  CONCAT(CAST(MIN(day_date) AS STRING), ' - ', CAST(MAX(day_date) AS STRING)) interval_days,
  SUM(metric) interval_metric
FROM `project.dataset.table`, days_range, months_range
WHERE EXTRACT(DAY FROM day_date) BETWEEN start_day AND end_day
AND EXTRACT(MONTH FROM day_date) BETWEEN start_month AND end_month
GROUP BY DATE_TRUNC(day_date, MONTH)
ORDER BY 1    

with result as

Row     interval_days               interval_metric  
1       2018-01-15 - 2018-01-20     244  
2       2018-02-15 - 2018-02-20     235  
3       2018-03-15 - 2018-03-20     204  
4       2018-04-15 - 2018-04-20     355    

if you want to check how same script will 'behave' for 28-30-31 days - try below

#standardSQL
WITH `project.dataset.table` AS (
  SELECT day_date, CAST(100 * RAND() AS INT64) metric
  FROM UNNEST(GENERATE_DATE_ARRAY('2018-01-01', '2018-12-31')) day_date 
), days_range AS (
  SELECT 25 start_day, 31 end_day
), months_range AS (
  SELECT 1 start_month, 4 end_month
)
SELECT 
  CONCAT(CAST(MIN(day_date) AS STRING), ' - ', CAST(MAX(day_date) AS STRING)) interval_days,
  SUM(metric) interval_metric
FROM `project.dataset.table`, days_range, months_range
WHERE EXTRACT(DAY FROM day_date) BETWEEN start_day AND end_day
AND EXTRACT(MONTH FROM day_date) BETWEEN start_month AND end_month
GROUP BY DATE_TRUNC(day_date, MONTH)
ORDER BY 1   

with result

Row     interval_days               interval_metric  
1       2018-01-25 - 2018-01-31     364  
2       2018-02-25 - 2018-02-28     227  
3       2018-03-25 - 2018-03-31     311  
4       2018-04-25 - 2018-04-30     308    

---

Hope this will help you to move forward