Mysql creation date (do not update when updating records)

Question

I am making a mysql table and I have one column with the current timestamp when updating. This is great because I can definitely see when someone is downloading something. But I do not want the column to change when they edited their load. It’s best to stick with one column named “creation date” and not have any updates, or go with two columns “date created and“ date modified ”- if so, what is best for the column attributes and PHP update statements?

Answer 1

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

As for installing 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 simplest option would be to use the insert trigger for this:

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

ETA:

You can use the TIMESTAMP field with the default value of CURRENT_TIMESTAMP and the ON UPDATE CURRENT_TIMESTAMP restriction ON UPDATE CURRENT_TIMESTAMP for LastModifiedDate. Unfortunately, you cannot have two such TIMESTAMP columns in the same table, so a trigger is needed to process the CreateDate column.

Answer 2

Well, it depends on how you use the data value, but in most cases it is recommended to use both created_at and updated_at as attributes for your table. I found both attributes useful in testing and debugging.

Answer 3

For more information, remember the date(); function date(); in PHP.

Also, I would definitely have the last modified date set to the default creation date.

Answer 4

Actually, this is not an answer, because I do not think that there is a best practice, but it is a question on which I had long thoughts.

Before even knowing what the table will be used for, I used 3 fields to create: Date created, Date changed and User who will tell me when the row was created, and by whom, then when this row was changed and who ... Um, no, I only have one user field. So what am I doing modifier and creator field? Then the user changes everything in the article, and the other user corrects the wrong treatment ... Who changed what?

In the end, I wanted to look at the history of the database entries, but after this very bad logic (date created, changed, user) I did not know anything. I know that you did not mention the user, but the problem remains for changes. What about when he left? You will not know.

But get me right, I’m not throwing a stone to anyone: most of my tables are still designed that way. However, this is a time for change, and I plan to create a history table, but I'm not yet sure about its structure.

So, if you do not mind giving your opinion on your problem, I would like to offer my two plans so far - not very different from each other - and see if people have an opinion about this. And it's a pity if I pollute your post, but I just jump on this opportunity: -p

History Table # 1:

Table RowID Action Date User

Documents 465 Created 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 & nbsp Removal; 2011-01-25 14:55:31 33

In this case, I wonder if there will be a use of a unique identifier, since I do not understand why I will look for a specific string. It will always be the history of a specific row for a particular table.

History Table 2:

Table RowID History

Documents 465 {[{{action: "creation", date: "2010-09-25 12:15:19", user: 25},
{action: "modify", date: "2010-09-25 18:03:38", user: 12},
{action: "modify", 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 row of the table through a JSON object (or serialized array). Therefore, I would not wonder about the primary key: table and rowId will certainly do the job. Even if I prefer this, the advantage of the first structure over this is that you can search for events in a timeframe, while this is not possible here.

The next step will be to decide if I am adding a text box to these structures in order to preserve DIFF in order to be able to restore the previous version. But this is another story.

I know that DateAdded and DateCreated may be the best solution for some situations, but if the goal is to know the history of your records in the database, I think that a dedicated history table is the best choice.

Answer 5

This is what I have, and it seems to work:

Name attributes default

created timestamp; CURRENT_TIMESTAMP
modified timestamp when updating CURRENT_TIMESTAMP CURRENT_TIMESTAMP