Calculate Month Difference in Google BigQuery

3
votes

BigQuery does not allow you to select the daypart MONTH in DATEDIFF.

Is there any other way to do this in BigQuery.

I can get days difference using the following but not months.

SELECT 
  OrderID,
  OrderDate,
  STRING( YEAR ( OrderDate )) + '-' + STRING(MONTH ( OrderDate )) as order_month, 
  UserID,
  FirstOrderDate
DATEDIFF( OrderDate, FirstOrderDate) as date_diff,
FROM [orders.orders] 
WHERE FirstOrderDate > DATE_ADD(CURRENT_TIMESTAMP(), -1, 'YEAR')
3
google-bigquery

3 Answers

4
votes

With Standard SQL you can use date_diff:

#StandardSQL
select date_diff(current_date, date '2018-03-06', month)
3
votes

How about this?

(12 * YEAR(t2) + MONTH(t2)) - (12 * YEAR(t1) + MONTH(t1))
    + IF (DAY(t2) >= DAY(t1), 0, -1)

Running it over a couple of examples, it looks like what you'd want:

SELECT 
(12 * YEAR(t2) + MONTH(t2)) - (12 * YEAR(t1) + MONTH(t1))
    + IF (DAY(t2) >= DAY(t1), 0, -1)
FROM 
    (SELECT TIMESTAMP("2010-12-01") as t1, TIMESTAMP("2011-01-01") as t2), // 1
    (SELECT TIMESTAMP("2010-12-02") as t1, TIMESTAMP("2011-01-01") as t2), // 0
    (SELECT TIMESTAMP("2010-12-01") as t1, TIMESTAMP("2011-12-01") as t2), // 12
    (SELECT TIMESTAMP("2010-10-21") as t1, TIMESTAMP("2015-01-10") as t2)  // 50
0
votes

With standard SQL date_diff could work, but if you get an error saying "DATE_DIFF does not support the MONTH date part at [n:m]code", that's because your it's timestamp rather than date. To solve this issue, first cast the timestamp to date, then use date_diff function. For example:

DATE_DIFF(CAST(OrderDate AS DATE), CAST(FirstOrderDate AS DATE), month) AS DATE_DIF