Insert current date when inserting

Question

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 the inserted column. It should never change.

When an entry is edited, the current date should be added to the edited column, and the date should be updated each time this entry is edited.

My approach was to define the data type date in both cases and change the standard value to CURDATE(). But instead, just insert CURDATE() as a string.

UPDATE Here 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.

Answer 1

Try modifying your schema as follows

`inserted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`edited` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Hope this helps.

Answer 2

Depending on your needs, this will suit you:

CREATE TABLE `test`.`testtab` 
  ( 
     `id`             INT NOT NULL auto_increment, 
     `some column`    VARCHAR(100) NULL, 
     `another column` VARCHAR(100) NULL, 
     `inserted`       DATETIME DEFAULT   CURRENT_TIMESTAMP, 
     `edited`         DATETIME ON UPDATE CURRENT_TIMESTAMP, 
     PRIMARY KEY (`id`) 
  ) 
engine = innodb;

Then only extract the date part when processing:

DATE_FORMAT(datetime, '%Y-%m-%d')

You can use triggers as a workaround to set the datetime field to NOW() for new inserts:

CREATE TRIGGER `triggername` BEFORE INSERT ON  `tablename` 
FOR EACH ROW 
SET NEW.datetimefield = NOW()

It should also work for updates