CODEWITHSUNDEEP
phpmysqltimestamp
benhowdle89
benhowdle89

Reputation: 37464

Mysql date created (not to update when record updated)

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?

Upvotes: 5

Views: 6107

Answers (5)

JoeKincognito
JoeKincognito

Reputation: 371

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

Upvotes: 2

Nabab
Nabab

Reputation: 2644

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.

Upvotes: 1

Jake Lee
Jake Lee

Reputation: 7979

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.

Upvotes: 1

Eric Petroelje
Eric Petroelje

Reputation: 60498

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.

Upvotes: 7

ajmartin
ajmartin

Reputation: 2409

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.

Upvotes: 2

Related Questions