DATETIME_DIFF Units Confusion

[Mark_Schad]

Hi,

I ran the below experiment with DATETIME_DIFF.

It seems the units argument only performs a simple division, but most implementations I have seen of this function always compare the actual units.

Am I missing something?

[Elias_Gomez_Sai]

The difference is just 1 day, that rounded to months is 0. You are not substracting months, but full dates (included time) specified in the unit (months in this case).

You could get what you are expecting using MONTH() in both dates before DATETIME_DIFF().

[Jeremy_Oglesby]

@Mark_Schad,

@Elias_Gomez_Sainz is right - just using DATETIME_DIFF() on two, unprocessed date fields is going to calculate the difference in the Unix time stamp, which is a just a big number that represents elapsed seconds since the beginning of the Unix time stamp “epoch” (January 1st, 1970, UTC).

So, contrary to your expectations, it is not comparing the number of the month from the first date to the number of the month in the second date, and performing a diff calc on those. Rather, it is getting the total difference in seconds between the two dates (as a whole), converting that to months through division, then rounding to the nearest integer, which in the case of a difference less than 30 days, will evaluate to “0 months”.

What @Elias_Gomez_Sainz is suggesting is doing a simple subtraction operation on the isolated month units, like this:

MONTH(Date1) - MONTH(Date2)

That will get you the difference between the month integers from the dates in question, BUT this will not be able to account for a difference across years, as you are removing years from the equation. So, for example,

MONTH(3/1/2018) - MONTH(12/1/2017) = -9

where if considering years, you might want it to return 4.

Does that help?

[Mark_Schad]

Seems to me like you’re not getting as much bang for your functional buck with that implementation, and for two reasons:

DATETIME_DIFF(x,y)/30.4 is less ambiguous than DATETIME_DIFF(x,y,“months”)

DATETIME_DIFF(x,y,“months”) is much simpler than MONTH(x) - MONTH(y) + 12*(YEAR(x)-YEAR(y)) versus the previous example, and would bring the function in line with DAX and T-SQL, in the interest of the principal of least astonishment.

https://msdn.microsoft.com/en-us/query-bi/dax/datediff-function-dax
https://docs.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-2017

[Jeremy_Oglesby]

Mark_Schad:

Am I missing something?

I’m sorry - I misunderstood your OP as a request for help understanding Airtable’s implementation.

I don’t work at Airtable, so I have no “implementation” interest to defend here. I was just trying to explain what I thought you were missing.