Generated Column with CURDATE()

Data Service
1

Why Generated Columns doesn’t support NOW() function?

I tried:

CURRENT_DATE()
CURRENT_TIMESTAMP()
CURRENT_TIMESTAMP()
CURDATE()
NOW()
LOCALTIMESTAMP

I’ve got error:

Update column failed. Expression of generated column ‘currentWeekDay’ contains a disallowed function.

I want to create simple column:

WEEKDAY(CURDATE())

2

One of the rules applied to generated columns is that the expression must use deterministic functions.

What is a deterministic function? Here’s a definition straight from the underlying database docs:

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: NOW(), CURRENT_DATE()

It is still possible to create a generated column as you describe it, however, instead of CURDATE() use the created or the update columns, for example:

WEEKDAY( created )

Regards,
Mark

3

In MariaDB, this works in the following way

There are two type of generated column: STORED and VIRTUAL.
You can do it using VIRTUAL type.

ALTER TABLE MY_TABLE ADD COLUMN MY_VIRTUAL_COLUMN_NAME VARCHAR(50) GENERATED ALWAYS AS (
    CURRENT_DATE 
) VIRTUAL;
4

@mark-piller We’re also trying to create a generated column which compares a date field to the current time… but this is apparently not possible. How would one create a VIRTUAL generated column in Backendless? Is it even possible?

5

The best available option is to use the value of either the created or the updated column (whichever applies) to get the “current time”.

6

Yeah but we do want the actual current time. Our situation is that we have users with credits on a pass that has an expiry date, and when that date passes they should loose their credits. Apparently that cannot be done. I think we should either set up a timer to run once a day to delete credits in the database based on expiry date, or we do something in the front end that checks expiry date and shows users “0 credits” if the expiry date is in the past.

7

Yes, a timer would be a better option.

As for the generated columns, they cannot use non-deterministic functions (i.e. functions that produce different results for multiple invocations).

1 Like