CODEWITHSUNDEEP
phpmysql
cookie
cookie

Reputation: 2728

Update a database column dynamically

mysql> describe break;
+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| id          | int(11)  | NO   | PRI | NULL    | auto_increment |
| start       | datetime | YES  |     | NULL    |                |
| end         | datetime | YES  |     | NULL    |                |
| duration    | datetime | YES  |     | NULL    |                |
| date        | datetime | YES  |     | NULL    |                |
| employee_id | int(11)  | NO   | MUL | NULL    |                |
+-------------+----------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

I would like to calculate (and update) the 'duration' column using the start and end values from the above table. For example:

UPDATE break SET duration=TIMEDIFF(start, end) WHERE employee_id=1;

Alas, this sets the duration column to 0000-00-00 00:00:00

mysql> select * from break\G;
*************************** 1. row ***************************
     id: 30
  start: 2013-08-06 15:43:17
    end: 2013-08-06 15:55:42
duration: 0000-00-00 00:00:00
   date: 2013-08-06 15:43:17
employee_id: 1
1 row in set (0.00 sec)

help

Upvotes: 1

Views: 105

Answers (2)

cmorrissey
cmorrissey

Reputation: 8583

Change durration to type TIME and then update your code

UPDATE break SET duration=TIMEDIFF(end, start) WHERE employee_id=1;

or you will be getting a negative durration

UPDATE: in response to another answer you can make a trigger that sets the duration every time the row is updated

DROP TRIGGER IF EXISTS `break_trigger`;
DELIMITER //
CREATE TRIGGER `break_trigger` AFTER UPDATE ON `break`
 FOR EACH ROW BEGIN
    SET duration=TIMEDIFF(end, start) WHERE employee_id= NEW.employee_id;
END
//
DELIMITER ;

Upvotes: 2

Bart Friederichs
Bart Friederichs

Reputation: 33511

From the documentation (emphasis mine):

TIMEDIFF() returns expr1 – expr2 expressed as a time value.

Change duration to type TIME and it should work.

Note that you are creating redundancy in your schema. This is usually a Bad Thing. What if end or start change and you do not update duration? Better use a view and return the TIMEDIFF calculated from end and start from there.

Upvotes: 4

Related Questions