MySQL Create table with Date Entered and Date Modified

Home Forums Tech Database MySQL Create table with Date Entered and Date Modified

This topic contains 0 replies, has 1 voice, and was last updated by  Mr. Mangus 3 years, 11 months ago.

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #4162

    Mr. Mangus
    Participant

    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

Viewing 1 post (of 1 total)

You must be logged in to reply to this topic.