Difference between two dates in dates using Google bigquery?

Question

How can I get the difference in days between 2 timestamp fields in Google Big Query?

The only function I know is Datediff which only works in Legacy SQL but I'm in Standard SQL.

For example: the difference between 20180115 to 20180220 is 36 days.

It's only in part because of Jan 31. It's also because of Feb 4. Anyway, isn't the function DATEDIF? — Strawberry, May 15 '18 at 13:31
How about `EXTRACT(DAY FROM DATE '20180220' - DATE '20180115')` - I'm just guessing here. — Strawberry, May 15 '18 at 13:42
No, because if they are in different months it will be wrong. — Evyatar Aviram, May 15 '18 at 13:43

komarkovich · Answer 1 · 2018-05-15T15:04:38.250

50

As per documentation, you should use DATE_DIFF function in standard SQL.

Your query should look like this:

SELECT DATE_DIFF(DATE '2018-02-20', DATE '2018-01-15', DAY) as days_diff;

result:

Row days_diff    
 1     36

edited May 15 '18 at 15:04

answered May 15 '18 at 14:48

komarkovich

2,223
10
20

score 4 · Answer 2 · answered May 16 '18 at 03:56

First you need to "translate" you string representation of date into date type using PARSE_DATE() function and then DATE_DIFF() function allows you to calculate difference/distance between two dates as in example below

#standardSQL
SELECT 
  DATE_DIFF(
    PARSE_DATE('%Y%m%d', '20180220'), 
    PARSE_DATE('%Y%m%d', '20180115'), 
    DAY
  ) days

produces:

Row days     
1   36

Difference between two dates in dates using Google bigquery?

2 Answers2