CODEWITHSUNDEEP
mysql
NorthSide
NorthSide

Reputation: 1499

MySQL - Trigger on Insert HOUR('time')

I have a table that contains a field 'timeduration'. Currently when I select the 'timeduration' value I'm doing HOUR('timeduration') to get the time in hours. I would like to do this calculation when inserting the data into the table, so putting the hour value into it's own column.

|  date    |  timeduration  |  hour
  12-12-14    01:00:00         1

I've looked into triggers but not sure how to about this, or os there an easier way I'm missing?

Thanks for your help!

Upvotes: 0

Views: 1075

Answers (1)

spencer7593
spencer7593

Reputation: 108370

Yes, a BEFORE INSERT and BEFORE UPDATE trigger would do it. And that's the only way to get MySQL to automatically populate the value in the hour column, based on the value assigned to the timeduration column.

Here's an example a BEFORE INSERT trigger.

 DELIMITER $$

 CREATE TRIGGER mytable_bi 
 BEFORE INSERT ON mytable
 FOR EACH ROW
 BEGIN
    SET NEW.hour = HOUR(NEW.timeduration);
 END$$

 DELIMITER ;

You'll likely also want a corresponding BEFORE UPDATE trigger, if you want to keep hour in sync when timeduration value is modified by UPDATE statement.

Upvotes: 1

Related Questions