[SOLVED] How to calculate ages in BigQuery?

How to calculate ages in BigQuery?

I have two TIMESTAMP columns in my table: customer_birthday and purchase_date. I want to create a query to show the number of purchases by customer age, to create a chart.

But how do I calculate ages, in years, using BigQuery? In other words, how do I get the difference in years between two TIMESTAMPs? The age calculation cannot be made using days or hours, because of leap years, so the function DATEDIFF(<timestamp1>,<timestamp2>) is not appropriate.

Thanks.

Solution

You can compute the number of days it would be if all years were 365 days long, take the difference, and divide by 365. For example:

SELECT (day2-day1)/365 
FROM (
  SELECT YEAR(t1) * 365 + DAYOFYEAR(t1) as day1,
         YEAR(t2) * 365 + DAYOFYEAR(t2) as day2 
  FROM (
    SELECT TIMESTAMP('20000201') as t1, 
           TIMESTAMP('20140201') as t2))

This returns 14.0, even though there are intervening leap years. If you want the final result as an integer instead of floating point, you can use the INTEGER() function to cast the result.

Note that if one of the dates is a leap day (feb 29) it will appear to be one year away from march 1, but I think this sounds like the intended behavior.