Mysql date created (not to update when record updated)

Question

I'm doing a mysql table and i have one column that has current timestamp on update. This is great because i can see exactly when someone uploads something. But i dont want the column to change when they edit their upload. Is it best to stick with one column named "date created" and have NO on update or go with two columns "date created & "date modified" - if so whats the best practice for column attributes, and PHP update statements?

Eric Petroelje · Accepted Answer · 2011-02-09T18:29:26.693

7

I usually like to have a separate "CreateDate" and "LastModifiedDate".

As for setting it, it would be nice if you could just set the default value for the CreateDate column to NOW(), but MySQL doesn't allow that.

So, the easiest option would be to use an insert trigger to do it:

CREATE TRIGGER tbl_insert BEFORE INSERT ON `tbl`
    FOR EACH ROW SET NEW.CreateDate = NOW(), NEW.LastModifiedDate = NOW();

ETA:

You can use a TIMESTAMP field with a default value of CURRENT_TIMESTAMP and the ON UPDATE CURRENT_TIMESTAMP constraint for the LastModifiedDate as well. Unfortunately you can't have two such TIMESTAMP columns on the same table, so the trigger is necessary to handle the CreateDate column.

edited Feb 09 '11 at 18:29

answered Feb 09 '11 at 18:21

Eric Petroelje

59,820
9
127
177

+1 for the trigger. Mysql does allow `Current_Timestamp` as a default option and/or `on update Current_Timestamp` - the limitation is that you can not use them at the same time. I think for simplicity, I would have the trigger only do the CreateDate and keep the LastModifiedDate attribute set to `on update Current_Timestamp` – kmfk Feb 09 '11 at 18:25
@kmfk - good point, forgot to mention that. Updated my answer. – Eric Petroelje Feb 09 '11 at 18:29
hah, this one is just nitpicking now - but in your edit: you CAN have multiple timestamp fields. you cannot have more then one which is using Current_Timestamp in default or on update. :] – kmfk Feb 09 '11 at 18:39
@kmfk - ha, thought of that too after I edited it, so changed "two TIMESTAMP columns" to "two such TIMESTAMP columns". maybe not entirely clear, but close enough :) – Eric Petroelje Feb 09 '11 at 18:49
-1 for mysql not allowing one timestamp column have a default value and a second to get the auto update value. Seems like a very common use case. – Ray Dec 23 '11 at 19:15

score 1 · Answer 2 · answered Feb 09 '11 at 18:28

1

Well it depends on how you are using the data value, but mostly it is a good practice to have both created_at and updated_at as attributes for your table. I found both the attributes helpful in testing, and debugging.

answered Feb 09 '11 at 18:28

ajmartin

2,379
2
26
42

score 1 · Answer 3 · answered Feb 09 '11 at 18:46

1

Just as extra info, remember the date(); function in PHP.

Apart from that, I'd definitely have a last modified date, set to creation date by default.

answered Feb 09 '11 at 18:46

Jake Lee

7,549
8
45
86

score 1 · Answer 4 · answered Feb 09 '11 at 23:35

This is not really an answer as I don't think there is A best practice, but that's a question on which I had long thoughts.

Before even knowing what a table would be used for, I used to create 3 fields: Date created, Date modified, and User, which would tell me when a row was created and by who, then when this row was modified and by who... Erm, no I have only one user field. So do I do a modifier and a creator field? Then a user changes everything in an article, and another user corrects a mispelling... Who has changed what?

In the end, I wanted to have a view over the history of database inputs, but following this very bad logic (date created, modified, user), I knew nothing. I know you didn't mention the user, but the problem remains for modifications. And what about when it's deleted? You won't know.

But get me right, I 'm not throwing the stone to anyone: most of my tables are still designed this way. Nevertheless it's time for change, and I plan to create a history table, but I am not sure yet of its structure.

So if you don't mind, while giving my opinion on your problem I'd like to suggest my two plans so far - not hugely different from one another - and see if people have an opinion about it. And sorry again if I'm polluting your post, but I'm just jumping on the opportunity :-p

History table #1:

Table RowID Action Date User

Documents 465 Creation 2010-09-25 12:15:19 25
Documents 465 Modification 2010-09-25 18:03:38 12
Documents 465 Modification 2010-12-28 14:15:30 25
Documents 465 Deletion 2011-01-25 14:55:31 33

In that case I wonder if there would be a use for a unique ID, as I don't see why I would be looking for a specific row. That will be always the history of a specific row for a specific table.

History table #2:

Table RowID History

Documents 465 {[{action:"creation",date:"2010-09-25 12:15:19",user:25},
{action:"modification",date:"2010-09-25 18:03:38",user:12},
{action:"modification",date:"2010-12-28 14:15:30",user:25},
{action:"deletion",date:"2011-01-25 14:55:31",user:33}]}

Here one row will show us the whole history of one table row through a JSON Object (or serialized array). So I wouldn't wonder about the primary key: table and rowId will definitely do the job. Even if I prefer this one, the advantage of the first structure over this one is that you can search for events in a timeframe, while it's impossible here.

Next step will be to decide if I add a text field to those structures to keep a DIFF in order to be able to restore a previous version. But that's another story.

I know DateAdded and DateCreated can be the best solution for some situations but if the goal is to know about the history of your DB entries, I think a dedicated history table is the best choice.

JoeKincognito · Answer 5 · 2014-04-30T18:17:18.840

This Is what I have and it seems to be working:

Name type attributes default

created timestamp CURRENT_TIMESTAMP
modified timestamp on update CURRENT_TIMESTAMP CURRENT_TIMESTAMP

Mysql date created (not to update when record updated)

5 Answers5

Table RowID Action Date User

Table RowID History