Putting a default value as a difference of two columns in Mysql while creating a table

Question

Can we create a table in Mysql server with a column as the difference between two other columns. So when those two columns are populated the third column will be computed from them.

CREATE TABLE LOAD(
START_TIME DATETIME,
END_TIME DATETIME,
ELAPSED_TIME DATETIME AS START_TIME-END_TIME
)

Something like this

Be wary of storing easily derivable data – Strawberry Jul 19 '20 at 07:41 — Strawberry, Jul 19 '20 at 07:41

score 0 · Answer 1 · answered Jul 19 '20 at 00:43

MySQL 8 does indeed support generated columns. So to have a generated column elapsed_time which has the seconds passed between a column start_time and end_time, you could use timestampdiff() like:

CREATE TABLE `load`
             (start_time datetime,
              end_time datetime,
              elapsed_time integer AS (timestampdiff(SECOND, start_time, end_time)));

(Note that load is a reserved word and therefore needs to be enclosed in backticks when used as an identifier.)
db<>fiddle

It is giving an error while creating the table. Incorrect syntax near keyword 'AS' — Sahil Vohra, Jul 19 '20 at 19:49

Putting a default value as a difference of two columns in Mysql while creating a table

1 Answers1