CODEWITHSUNDEEP
mysql
Kei
Kei

Reputation: 315

MySQL - Automatically update total hours difference based on two other datetime columns

I want to be able to automatically update a column, totalHours based on two other date times. I am aware that I can do this with a query DATEDIFF(), but I want to be able to do this automatically, if possible, a default expressions such as CURRENT_TIMESTAMP and CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP which are automatically maintained by the DB on create and on update.

(I tried putting the DATEDIFF() as an expression but that didn't work, not surprising)

enter image description here

Upvotes: 0

Views: 55

Answers (1)

Xenos
Xenos

Reputation: 3507

You should use a VIEW, or a VIRTUAL COLUMN since the totalHours will make data redundancy if you store it right away.

View

CREATE ALGORITHM = UNDEFINED VIEW `tableview` AS SELECT 
*, 
DATE_DIFF(startEndTime, startDateTime) AS totalHours
FROM blabla
;

That way, you will have the totalHours always up-to-date without data redundancy. You can rename the current table (fi: to blabla_table) and create the VIEW with the current name of the table (fi: blabla). That way, you won't have to update all queries using the TABLE so they can now use the VIEW.

Virtual column

totalHours DATE AS (DATE_DIFF(startEndTime, startDateTime))

See the doc at http://dev.mysql.com/doc/refman/5.7/en/create-table-generated-columns.html

Upvotes: 1

Related Questions