Automatically storing last insert / updating time of record in MySQL field

We can keep one date & time field in a record of MySQL table which automatically gets updated with current time and date when the record is added.
For example we are adding names to a user or member table. In the member table there will be more fields like name, email, password etc. but for simplicity we will use three fields only. One field is id which is a auto increment field, next one is name field and last tm is a datetime field. Here once we add name to the table the id automatically gets generated as it is an auto increment field and the field tm stores the date and time value showing the time of record insertion.

We are not inserting current date time by calculating through PHP to the table like given here.

While creating the table we will set the tm field ( DATETIME) Attributes to ON UPDATE CURRENT_TIMESTAMP and Default value to NOW().

See the MySQL dump to create the table here.

CREATE TABLE `test_time2` (
  `id` int(2) NOT NULL auto_increment,
  `name` varchar(20) NOT NULL,
  `tm` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

Structure of the date & time field for auto updating

Now once the table is created use this command to add one record. Note that we are only adding the name field value.

insert into  test_time2 (name) values('my_name')

After executing the query you can see tm field has the current date and time value, as usual the id field has the new unique id for the record. Now let us try to update the same record by using this query.

update test_time2 set name='my_name1' where name='my_name'

After using the above query you can see the value of tm files also changed and now it has the new date and time value of when record was updated.

If you are giving option to change password in a member table then such a field can be kept to keep the last updated time of the record.

Another application is if you are storing the login time of the members. You can update one field and show the visitors their last login time. Here before updating the current login info you have to collect the previous record and display.

Altering table structure to add date time with record insert

We can change the structure of the table and add one new field to store date and time automatically at the time of record insert. If our table is already having data then we have to set this new field as NULL as already existing records won't have data for this new field.
Here is the query

ALTER TABLE  `test_time` ADD  `dt` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP

Subscribe to our YouTube Channel here

Last updating time Storing in MySQL field

Altering table structure to add date time with record insert

Subscribe