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.

Answer 1

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?

Sign up