Insert current date on insert

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 a new entry is inserted, the current date should be added into the column inserted. It should never be changed.
When the entry is edited, the current date should be added into the column edited and it should update every time this entry is edited.

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

Update This is an example 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; 

Though, I'm not sure about the data types.

Answer 1

Based on your needs this will work for 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 while processing just extract date part:

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

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

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

it should work for updates too

Answer 2

Try modifying your schema like below

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

Hope this should help you out.