Set default value of a timestamp column to a sum of 2 timestamp in mySQL?

Question

I've created a table in mySQL like this:

CREATE TABLE IF NOT EXISTS `LibraryManager`.`Card` (
  `card_id` INT NOT NULL AUTO_INCREMENT,
  `card_registerDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `card_expiredDate` TIMESTAMP,
  PRIMARY KEY (`card_id`))
ENGINE = InnoDB;

Now I want to set the default value of card_expiredDate to card_registerDate + 30 or CURRENT_TIMESTAMP + 30 (30 day from register date). Is there any way to do that? Thank you very much for reading this.

But why store a derived value? – Strawberry Apr 23 '16 at 08:17 — Strawberry, Apr 23 '16 at 08:17

score 1 · Answer 1 · answered Apr 23 '16 at 08:09

1

Create an AFTER INSERT trigger, it will do the job for you:

DELIMITER $$
CREATE TRIGGER set_expiration_date
AFTER INSERT ON `LibraryManager` FOR EACH ROW
BEGIN

    UPDATE LibraryManager 
    SET card_expiredDate = DATE_ADD(NEW.card_registerDate, INTERVAL 30 DAY)
    WHERE card_id = NEW.card_id;

END;
$$
DELIMITER ;

PS: I haven't tested it, if you get any error do let me know.

answered Apr 23 '16 at 08:09

Shaharyar

12,254
4
46
66

1

It's better to use `BEFORE INSERT` trigger and calculate value before insert instead of perform second `UPDATE` query. – Andrew Apr 23 '16 at 08:19

score 1 · Accepted Answer · edited May 23 '17 at 12:23

Setting the default value for expiry column using create statement is not possible as such, instead use trigger. For that you will have to slightly modify you create statement. Just change the datatype of expiry column, your new query:

CREATE TABLE IF NOT EXISTS `LibraryManager`.`Card` (
`card_id` INT NOT NULL AUTO_INCREMENT,
`card_registerDate` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`card_expiredDate` DATETIME,
PRIMARY KEY (`card_id`))
ENGINE = InnoDB;

and then fire the following trigger

CREATE TRIGGER before_insert_library_card
BEFORE INSERT ON `LibraryManager`.`Card` 
FOR EACH ROW
SET new.card_expiredDate = adddate(CURRENT_TIMESTAMP,30);

hope this helps.

P.S: Triggers slow down your insert operations(or whatever operation they are applied before/after). I suggest you set these values programmatically using php/python or whatever the backend you are using.

Set default value of a timestamp column to a sum of 2 timestamp in mySQL?

2 Answers2