Need help with generated column DATEDIFF against current date/time

General
1

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)

2

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

3

Hi,
Was wondering if the next release which is mentioned in this post had been effectively released ?
Indeed, I am trying to do the same using DATEDIFF(created, NOW()) in a generated column, but can’t manage to find a way for it to work.
Thanks for your help.

4

Hi Nicolas,

What i am about to suggest is not ideal but may help.

Create a timer in cloud code at what ever interval you desire to update a “Now” Column in the table you desire and then do a generated column from that.

Kind Regards,
Raymond

5

@Nicolas_REMY ,

Nothing had changed since that time since this is the limitation of the underlying MySQL DB Server itself and not of Backendless platform. If you really need to emulate behavior of NOW() function for generated column I would recommend you to try approach suggested by Raymond.

Regards, Andriy

6

Thank you @Andriy_Konoz for your reply.
I was only asking because it was my understanding that you had a workaround in the works and to be released soon after the question was originally asked.
I understand the approach suggested by @Raymond_Woodley would indeed be some kind of workaround, but it’s just too clumsy and heavy for my need. I will have to try it some other way, thanks.