set timestampdiff() as default value in MySQL

Question

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())'

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
– DaveMac, Commented Aug 15, 2015 at 7:30

user5229643 · Accepted Answer · 2015-08-15 07:19:13Z

2

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
;

answered Aug 15, 2015 at 7:19

user5229643

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

set timestampdiff() as default value in MySQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related