set timestampdiff() as default value in MySQL

0
votes

I have a column of type int(11) in MySQL. I want to set default value to

timestampdiff(second,'2000-01-01 00:00:01',current_timestamp())

Right now I'm getting this error:

Operation failed: There was an error while applying the SQL script to the database. Executing: ALTER TABLE DB_NAME.TABLE_NAME CHANGE COLUMN time time INT(11) NULL DEFAULT 'timestampdiff(second,\'2000-01-01 00:00:01\',current_timestamp())' ;

ERROR 1067: Invalid default value for 'time' SQL Statement: ALTER TABLE DB_NAME.TABLE_NAME CHANGE COLUMN time time INT(11) NULL DEFAULT 'timestampdiff(second,\'2000-01-01 00:00:01\',current_timestamp())'

1
mysqldatabasedefault-value
I think gordans answer is your best route. It is unlikely mysql will evaluate an expression for the table definition - the table will be already defined. – DaveMac

1 Answers

2
votes

I believe the only way to do it here is to make a trigger

DELIMITER ;

CREATE TRIGGER default_time_trigger
BEFORE INSERT ON TABLE_NAME
FOR EACH ROW BEGIN
    IF (NEW.`time` IS NULL) THEN
        SET NEW.`time` = timestampdiff(second,'2000-01-01 00:00:01',current_timestamp());
    END IF;
END
;