Need help with generated column DATEDIFF against current date/time

Looking to create a generated column to compare current time against a datetime (myDate) column. MySQL syntax is not working as expected. Getting “function not allowed” error.

DATEDIFF(CURDATE(), myDate)

Hi @Derek_Turner ,

Unfortunately you encountered limitation of DB server itself.
From MySQL documentation 13.1.18.7 CREATE TABLE and Generated Columns:

Generated column expressions must adhere to the following rules. An error occurs if an expression contains disallowed constructs.

  • Literals, deterministic built-in functions, and operators are permitted. A function is deterministic if, given the same data in tables, multiple invocations produce the same result, independently of the connected user. Examples of functions that are nondeterministic and fail this definition: CONNECTION_ID(), CURRENT_USER(), NOW()

CURDATE belongs to the same category as NOW function.

At the current moment there is no workaround for your case but it will be added in the next release which we are expecting to have at the beginning of the next month. In it we will add support of DATEDIFF function in where clause and dynamic properties and you will be able to bypass this limitation using dynamic property with similar expression.

Sorry for inconvenience.

Regards, Andriy