MySQL auto-store datetime for each row

Question

In MySQL, I'm sick of adding the columns dt_created and dt_modified (which are date time stamps for creation and last modified respectively) to all the tables I have in my database.

Every time I INSERT or UPDATE the database, I will have to use the NOW() keyword. This is going all over my persistence.

Is there any efficient alternative where MySQL can automatically store at least the datatime of the row that is inserted and let me retrieve it?

My poor grammar lurks on the dark depths of Stack Overflow. Thank you @Eric Leschinski. — mauris, Oct 29 '14 at 17:43

score 111 · Accepted Answer · edited Sep 18 '18 at 07:52

111

You can use DEFAULT constraints to set the timestamp:

ALTER TABLE
 MODIFY dt_created datetime DEFAULT CURRENT_TIMESTAMP

ALTER TABLE
 MODIFY dt_modified datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

Then you wouldn't have to specify NOW() in your INSERT/UPDATE statements.

Reference: TIMESTAMP properties

edited Sep 18 '18 at 07:52

Charlie

22,886
11
59
90

answered Dec 19 '09 at 05:58

OMG Ponies

325,700
82
523
502

ahh I didn't know you can use something like `ON UPDATE CURRENT_TIMESTAMP` – mauris Dec 19 '09 at 06:03
4

**Aditional info:** One TIMESTAMP column in a table can have the current timestamp as the default value for initializing the column, as the auto-update value, or both. It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column. http://dev.mysql.com/doc/refman/5.0/en/timestamp-initialization.html – alditis Oct 21 '13 at 00:02
7

`DEFAULT ON UPDATE CURRENT_TIMESTAMP` is not valid SQL. It must be either `dt_modified datetime ON UPDATE CURRENT_TIMESTAMP` or `dt_modified datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP` If you still claim it is valid, then perhaps MySQL Workbench has a problem with it that should be mentioned. – James Wilkins Jul 24 '17 at 02:40

score 15 · Answer 2 · answered Apr 04 '14 at 13:31

15

If you're using phpmyadmin you can do this by :

enter image description here

answered Apr 04 '14 at 13:31

Ouadie

13,005
4
52
62

score 11 · Answer 3 · answered Feb 20 '10 at 10:13

11

ALTER TABLE  `tablename` CHANGE  `dt`  `dt` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

should be the correct code.

answered Feb 20 '10 at 10:13

Kim Stacks

10,202
35
151
282

score 3 · Answer 4 · answered Mar 29 '13 at 13:04

Well, you can't have both:

mysql doc:

It is not possible to have the current timestamp be the default value for one column and the auto-update value for another column.

Sad, isn't it?

You could however use null instead of now() following this tip

score 1 · Answer 5 · edited May 23 '17 at 11:47

1

Similar question was asked here "Timestamp for MySQL" the timestamp field will update every time it is accessed. You might also consider a Trigger placed on the table in question to automatically populate those fields for you. Depending on the environment some shops/businesses do not like the use of triggers and so you might have to find alternate work arounds.

edited May 23 '17 at 11:47

Community

1
1

answered Dec 19 '09 at 05:58

GrayWizardx

19,561
2
30
43

score 0 · Answer 6 · edited Apr 28 '17 at 13:41

0

In phpmyadmin you can set enter image description here

OR use this query

ALTER TABLE  `tablename`
    CHANGE  `dt_created`  `dt_created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

edited Apr 28 '17 at 13:41

Dmitry Egorov

9,542
3
22
40

answered Apr 28 '17 at 13:21

user3110005

21
5

score 0 · Answer 7 · answered Nov 08 '19 at 01:49

0

could be set as default an on update of rows

ALTER TABLE `tablename` CHANGE `dt` `dt` TIMESTAMP on update CURRENT_TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP;

answered Nov 08 '19 at 01:49

Jan Mark

147
1
2
10

MySQL auto-store datetime for each row

7 Answers7

Linked

Related