In this blog post, I elaborate on two specific operations you’ll run into when using dates in BigQuery. Subtracting an interval from a data and calculating the difference between two dates.
Subtracting a unit
Subtracting a specific amount of days, weeks, months, quarters, or years from a date can be done using the DATE_SUB function. The first argument takes a date and the second argument takes an interval, a numeric value, and a unit. The supported units (DATE_PART) are:
- Days: DAY
- Weeks: WEEK
- Months: MONTH
- Quarters: QUARTER
- Years: YEAR
Here’s an example on how to do it:
SELECT DATE_SUB('2020-01-28', INTERVAL 6 DAY) as six_days_agoThis is fairly different from (for example) MySQL, where you can simply use mathematical operators on dates using an INTERVAL.
Subtracting two dates (Difference)
Subtracting two dates from each other (i.e. calculating the difference) uses the DATE_DIFF function, where the second argument is subtracted from the first argument. The third argument is the unit (DATE_PART) in which you’d like to receive the calculation.
Supported units:
- Days: DAY
- Weeks: WEEK
- Specific weekdays: WEEK(…) e.g. WEEK(SUNDAY)
- Weeks, starting on monday: ISOWEEK
- Months: MONTH
- Quarters: QUARTER
- Years: YEAR
- First mondays of the year: ISOYEAR
This is a simple example that calculates the difference between two dates:
SELECT DATE_DIFF('2020-01-28', '2019-08-28', DAY) as days_in_betweenThis is how to subtract dates in BigQuery.