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.

hu?!? the text/string `'CURDATE()' is allowed to be inserted into a date datatype column? That seems unlikely, unless your DB is really hosed. All I can think of is remove the tics from around curdate() on your statement. and validate inserted and edited are both "Date" data types. — xQbert, Nov 17 '16 at 13:44
http://stackoverflow.com/questions/9032047/datetime-mysql-select-only-date — Muhammad Muazzam, Nov 17 '16 at 14:14

Just_Do_It · Accepted Answer · 2016-11-17T14:38:50.870

5

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

edited Nov 17 '16 at 14:38

answered Nov 17 '16 at 14:09

Just_Do_It

821
7
20

Is there any chance, to just store the date, without the time? – Evgenij Reznik Nov 17 '16 at 14:10
The DEFAULT clause specifies a default value for a column. With one exception, the default value must be a constant; it cannot be a function or an expression. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. The exception is that you can specify CURRENT_TIMESTAMP as the default for a TIMESTAMP column – Just_Do_It Nov 17 '16 at 14:33

score 3 · Answer 2 · answered Nov 17 '16 at 13:49

3

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.

answered Nov 17 '16 at 13:49

Viki888

2,686
2
13
16

Is there any chance, to just display the date, without the time? – Evgenij Reznik Nov 17 '16 at 13:59
when you fetch, you can trim the time as `date(inserted)` and `date(edited)`. – Viki888 Nov 17 '16 at 14:07
use a `DATE` field instead of `TIMESTAMP` – Juan Carlos Oropeza Nov 17 '16 at 14:08
@JuanCarlosOropeza: Doing so, I get this error: `Invalid default value for 'inserted'` – Evgenij Reznik Nov 17 '16 at 14:10

score 1 · Answer 3 · answered Nov 17 '16 at 16:10

Unfortunately you can't default a MySQL DATE field to curdate(). Following options are available with mysql:

As of MySQL 5.6.5, you can use the DATETIME type with a dynamic default value. For e.g. inserted DATETIME DEFAULT CURRENT_TIMESTAMP
Prior to 5.6.5 TIMESTAMP (only one auto update per table can exit) will update the field whenever the field is modified.

Reference: http://dev.mysql.com/doc/refman/5.7/en/timestamp-initialization.html

score -1 · Answer 4 · answered Nov 17 '16 at 13:52

1) We'd need to see your current query for how you insert the new row.

2) Once the row is inserted, you can changed the column edited to "on update CURRENT TIMESTAMP" within phpmyadmin. To do this, go to your table, click the "structure" tab above the table, find the column "edited" and click "change" on that column. Then, under the "attributes" setting, you should be able to choose "on update CURRENT TIMESTAMP". Then just save the changes and test it.

Insert current date on insert

4 Answers4

Linked

Related