retrieve CURRENT_TIMESTAMP value with MySQL

[Aienstaien]

How to retrieve CURRENT_TIMSTAMP using MySQL query, this is because i want to retrieve all the the records and the time during which the records were edited, this is just to keep track of which records have been modified or changed so please let me know if anyone aware it. I think this would be one kind of logs which we could maintain for the records we have edited.

[RichiRich]

I will provide an trigger example that you apply in your application which will trigger as per the time set and provides you the records detail, but this will be an simple idea about how to get the data retrieval if you require some additional field to be retrieve add your own fields as per the table fields.

CREATE TRIGGER t_ins BEFORE INSERT ON t
FOR EACH ROW SET NEW.date_expire= ADDDATE(CURDATE(),INTERVAL 1 MONTH);

[Timothi]

As shown in the following table you need to assign the table details and the table parameters and then you need to set the timestamp on the fields you want to have the timestamp.

CREATE TABLE IF NOT EXISTS `staff` (
  `idstaff` mediumint(8) unsigned NOT NULL auto_increment,
  `name` varchar(45) default NULL,
  `username` varchar(45) NOT NULL,
  `password` varchar(45) default NULL,
  `role` varchar(20) default NULL,
  `lastchange` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`idstaff`,`username`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

[Shell]

The reason it uses DATETIME is because MySQL has very special meanings for "TIMESTAMP" and you may use`entry_date` TIMESTAMP default CURRENT_TIMESTAMP which is supported by MySQL. As for the parsing of CURRENT_TIMESTAMP, we can add an exception that support for an default expression mentioned above.