Mode release notes
Mode release notes
mode.com

Updated behavior of function in Calculated Fields

On Tuesday, July 5th, we will update the DATEDIFF function in Calculated Fields. The new functionality will more closely match the way this is calculated in common databases such as MySQL, Redshift, and SQL Server.

Currently, DATEDIFF uses complete partitions to estimate the date difference. It looks at the time difference between two dates in the specified time unit, and then returns the number of full units that have passed.

The new behavior will use incomplete partitions to estimate the date difference. It will count how many times the “clock” has crossed the threshold that increases the unit count for that specified time unit. For example, going from 23:59:59 to 00:00:00 will increase the day count by 1. See examples on our Help Site.

Please note: When this change is made, existing visualizations may change by one (1) unit, such as minutes, days, or weeks, if they were previously calculated using partial units. We recommend reviewing a few of your most commonly viewed reports that use DATEDIFF to see if the changes described above will impact the results.

If you determine that you need to make changes based on this updated functionality, you can do one of two things:

  • Move the logic out of the Calculated Field and into your query. This can be done at any time.

  • Wait until we roll out the changes on Tuesday, July 5th, and then make the appropriate adjustment to the Calculated Fields.

Please reach out to our Support team if you need help identifying potentially impacted reports.