With a created by timestamp, is it better to have the DB manage it, or the application?

Question

We have your bog standard Java app under development, and a lot of the records we're creating (Hibernate entities in MySQL) have 'created' and 'modified' timestamps on them.

Now, me and one of the developers disagree - I believe that both of those fields should have a MySQL default of CURRENT_TIMESTAMP, and then the modified can be changed by the app. He wants both managed by the app.

Is there a compelling reason for either decision? I can't see why you'd want to add more explicit steps to the code, unless for some reason you were concerned about your servers (db, application) having inconsistent timestamps.

Answer 1

It makes sense to allocate all timestamps in the same tier for consistency.

This answer to a related question gives a nice example of how to do it in Hibernate.

Answer 2

• use DB timestamps if you can sacrifise portability. Also, if very small differences would matter to the application and you are going to have more than one application server / a cluster, you can have problems with synchronizing the nodes.
• use the application to generate the timestamps in case it is not certain the the chosen DB will stay, or if you are using multiple databases - for example MySQL for production, HSQLDB for unit-tests.

If none of these arguments apply, use the one that's easier for you (or the one that more developers vote for)

If you go for the application handling, either go for Pascal Thivent's suggestion with @PreUpdate, or have your field set with a default value, like:

private Calendar date = Calendar.getInstance();

Answer 3

I would handle this from the code and use Hibernate/JPA's @PrePersist and @PreUpdate callbacks:

@PreUpdate
@PrePersist
public void setTimeStamps() {
    modified = new Date();
    if (created==null) {
      created = new Date();
    }
}

Main reason: portability (i.e. this will work with another database, for example in a testing context, without any database magic, no DEFAULT, no trigger, nothing).

Answer 4

I would use triggers and store procedures to maintain these fields. So put the logic in the DB.