MySQL Create table with Date Entered and Date Modified

[Mr. Mangus]

In MySQL I needed to create table with two date fields.
One should serve as date entered, and one as date modified.
But you can’t have two fields od type TIMESTAMP in one table.
So, here is how I did a workaround:

CREATE TABLE tabContacts
( ID           INT UNSIGNED NOT NULL auto_increment,
  LastName     VARCHAR(32) NOT NULL default '',
  FirstName    VARCHAR(32) NOT NULL default '',
  Company      VARCHAR(64) NOT NULL default '',
  SearchType   VARCHAR(16) NOT NULL default '',
  ActivityCode VARCHAR(16) NOT NULL default '',
  Status       VARCHAR(16) NOT NULL default '',
  AccountNum   VARCHAR(32) NOT NULL default '',
  DateEntered  DATETIME DEFAULT CURRENT_TIMESTAMP,
  DateModified TIMESTAMP,
  KeyWords     VARCHAR(64) NOT NULL default '',
  MasterRemark VARCHAR(128) NOT NULL default '',
  PrintForHim  TINYINT NOT NULL default 1,
  PrintForHer  TINYINT NOT NULL default 0,
  ModifiedBy    VARCHAR(64) NOT NULL default '',
  PRIMARY KEY (ID),
  KEY (LastName, Company)
 ) CHARSET utf8;

As you can see, I designated one filed as DATETIME and the other as TIMESTAMP. To test how this works, I inserted a test record into this table:

INSERT INTO tabContacts(LastName, FirstName, Company, ModifiedBy) 
                   VALUES ('Test','1234','2345','34567')

and the result was: DateEntered: 2016-01-08 14:39:27 DateModified: 0000-00-00 00:00:00

Now I need to test an update. Will update auto-update DateModified field? Here is an update statement:

UPDATE tabContacts SET LastName = ‘TestUpdate’
WHERE msLastName = ‘Test’

Let’ look at DateEntered and DateModified. Nothing changed. Both fields stayed the same:
DateEntered: 2016-01-08 14:39:27 DateModified: 0000-00-00 00:00:00

[Author]

Posts