Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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.

like image 331
Jason Maskell Avatar asked Jan 25 '10 07:01

Jason Maskell


2 Answers

  • 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();
like image 177
Bozho Avatar answered Sep 22 '22 20:09

Bozho


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).

like image 38
Pascal Thivent Avatar answered Sep 20 '22 20:09

Pascal Thivent