mysqlaltergenerated

When I run TIME_TO_SEC to modify a GENERATED column, it throws an error although TIME_TO_SEC works fine independently in a SELECT statement

I am running the following MySQL script and it throws an error, Could someone tell me what is wrong?

"ALTER TABLE class_sessions_archive MODIFY COLUMN session_time_computed int(11) AS TIME_TO_SEC(ends_on-starts_on)"

The error message in MySQL remains cryptic, however, I paste it here:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TIME_TO_SEC(ends_on-starts_on)' at line 1

All I need is to take the difference of two dates and store it in seconds in a new column. I made a new column and now want to modify it.

Solution

  • The expression should go in parentheses:

    ALTER TABLE class_sessions_archive
    MODIFY COLUMN session_time_computed int(11) AS (TIME_TO_SEC(ends_on - starts_on));