I have a table that requires two different current times. First, I have an insert procedure that inserts actionnumber, msgSentFrom_F_ID, msgSentTo_M_ID and sentDate. Secondly, update the update process of respondDate. My problem is that when I update respondDate, sentDate is updated to the same time as when I update respondDate. What did I do wrong? (My intention is that I want the sent date to be the current time when I insert, and another current time when I update the response date.)
CREATE TABLE IF NOT EXISTS actions ( actionnumber INT AUTO_INCREMENT PRIMARY KEY, msgSentFrom_F_ID INT, msgSentTo_M_ID INT, sentDate TIMESTAMP, respondDate TIMESTAMP NULL, FOREIGN KEY (msgSentFrom_F_ID) REFERENCES femaleUsers(femaleuserId) FOREIGN KEY (msgSentTo_M_ID) REFERENCES maleUsers(maleuserId) ); DELIMITER // create procedure (param_F_ID INT,param_M_ID INT,Sdate TIMESTAMP) BEGIN INSERT INTO actions (msgSentFrom_F_ID, msgSentTo_M_ID, sentDate) VALUES (param_F_ID,param_M_ID,Now()); END; // DELIMITER ; CALL insert_actions ('5','5',NOW()); DELIMITER // create procedure update_respondDate (param_ActionNum INT, param_respondDate TIMESTAMP) BEGIN UPDATE actions set respondDate = param_respondDate WHERE actionnumber = param_ActionNum; END; // DELIMITER ; CALL update_respondDate('6',NOW());
Sounds like you disabled the system variable
explicit_defaults_for_timestamp
. Documentation explains this result: p>Since
sentDate
is the firstTIMESTAMP
column in the table, it will automatically be set to the current time whenever you make any changes to that row.