How to find specific holiday date that varies by year in BigQuery?-CodePudding

I'm trying to query for dates that come the week before Labor Day. Labor Day is a US federal holiday on the first Monday of every September. I'm trying to select labor day and the full week before labor day. I'm somewhat close with this sort of query

select * from `bigquery-public-data.ghcn_d.ghcnd_1991` 

where extract(month from date) = 9 
and extract(dayofweek from date) = 2  
and extract(week from date) = 36

But it's not always the 36th week, sometimes its the 35th (so the query above is wrong).

I'm guessing I'll have to do a date subtraction in order to get the full week before labor day...but for now I just need help finding how to query the first Monday of every September.

CodePudding user response：

This is one approach:

select    t.*
from      `bigquery-public-data.ghcn_d.ghcnd_1991` t
     join (
            select    format_date('%Y%m',date) as yr_mo,
                      date_sub(min(date), interval 1 week)  as week_before,
                      min(date) as first_monday
            from      `bigquery-public-data.ghcn_d.ghcnd_1991`   v
            where     extract(dayofweek from date) = 2 
                  and extract(month from date) = 9
            group by  format_date('%Y%m',date)
          ) v
       on t.date between v.week_before and v.first_monday;

This presumes you want all rows of the table where the date is Labor Day or within the week leading to Labor Day.

CodePudding user response：

Because it is the first Monday of September you can perform a MIN on date within a CTE, or a subquery, to get Labor Day and the prior week:

WITH labor_day as (
  select MIN(date) date
  from `bigquery-public-data.ghcn_d.ghcnd_1991` 
  where extract(month from date) = 9 
  and extract(dayofweek from date) = 2  
)

SELECT distinct ghcnd.date
FROM `bigquery-public-data.ghcn_d.ghcnd_1991` ghcnd
INNER JOIN labor_day 
 on ghcnd.date between labor_day.date-7 and labor_day.date