Subtracting dates in BigQuery SQL

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_ago

This 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_between

This is how to subtract dates in BigQuery.