I have a table with the following structure:
+----+-------------+----------------+------------+------------+ | id | some column | another column | inserted | edited | +----+-------------+----------------+------------+------------+ | 1 | ... | ... | 2014-08-15 | 2016-03-04 | | 2 | ... | ... | 2015-09-16 | 2016-10-07 | | 3 | ... | ... | 2016-10-17 | 2016-11-16 | +----+-------------+----------------+------------+------------+
When inserting a new entry, the current date should be added to theinsertedcolumn. It should never change.
When an entry is edited, the current date should be added to theeditedcolumn, and the date should be updated each time this entry is edited.
My approach was to define the data typedatein both cases and change the standard value toCURDATE(). But instead, just insertCURDATE()as a string.
UPDATEHere is a sample query:
CREATE TABLE `test`.`testtab` ( `id` INT NOT NULL auto_increment, `some column` VARCHAR(100) NULL, `another column` VARCHAR(100) NULL, `inserted` VARCHAR(100) NULL DEFAULT 'CURDATE()', `edited` VARCHAR(100) NULL DEFAULT 'CURDATE()', PRIMARY KEY (`id`) ) engine = innodb;
I'm not sure about the data type, though.
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
Try modifying your schema as follows
Hope this helps.
Depending on your needs, this will suit you:
Then only extract the date part when processing:
You can use triggers as a workaround to set the datetime field to NOW() for new inserts:
It should also work for updates