I am under the impression that having created_at or updated_at columns seems to be a best practice in database design. Frameworks like "Ruby on Rails" and "Laravel" both support that.

I know what they are and I guess one main use case is when my data is corrupted I can check these columns to find out when that happened. But I also feel that is more like in theory because I definitely have other ways to find out why and when the data is corrupted.

So can someone cast some light on the benefit of having them in database design? Maybe it can be explained with some example?

4 Answers 4


Columns like these should not be added because "the framework makes it easy" or because of some cargo-cult "best-practice" illusion. They should be added in case there is a real requirement, a real use case for them, something requested by a user or stakeholder.

For example, in a customer database, a company may keep track about their customers and business partners as well as their contact persons, by manually entering the data from several sources like mail, email, phone calls and information from the web. Unfortunately, such data tends to age over time and loose its currentness. So they may have a process to validate all customer data which hasn't been updated in a period of more than 3 years, for example. And I guess it is pretty be self-evident how columns like "created_at" or "updated_at" can be used to support such a process.

Or, lets say one implements a forum software or blogging system, or a system like Stackoverflow, where users can enter and edit their posts. In these systems, it is a usual requirement to keep track of when the posts where entered, or when they were changed last time, and show that information to the users of the system.

So in general, whenever a system manages data where it is important for the users to know the age or currentness of the data, introducing timestamp columns becomes necessary. And since in larger systems this can affect multiple tables and the implementation can often be made in a uniform way, it is not very surprising that certain frameworks provide support for this whole category of requirements.

  • 3
    An extra reason it's often kept is because people usually only realize they need historical data after the thing they wanted to know about happened, so it's kept "just in case". And this one happens to be useful often enough that it's become a cargo-cult practice.
    – Erik
    Sep 3, 2020 at 8:44
  • 3
    @Erik: historical data requires a lot more than these time stamp columns, it requires to keep track of old records and not deleting them, and it requires any process in the system to distinguish between "old records" and "current ones". This is a huge topic and goes far beyond what was asked in the question, that's why I intentionally tried not to mention it.
    – Doc Brown
    Sep 3, 2020 at 8:48
  • 1
    Another use is for systems which require that processing moves from state A to state B, without "forgetting" about the update that happened between read and write. Forcing the requirement that an update to a record, not only match the guid, but also has the same original time stamps (and that these are changed post update) can allow optimistic locking in a massive parallel system. The long running pieces of work will know when they make their update that something changed.
    – Kain0_0
    Sep 3, 2020 at 9:13
  • 1
    Not everyone appreciates a question which boils down to "because bob and alice did it".
    – Kain0_0
    Sep 3, 2020 at 9:15
  • 2
    ... don't forget "who did what and when" is person-related data. In some environments, it is crucial to log this information. In some other environments, it could become quickly illegal. So what in some domains may be a "best practice" can turn out as a really bad practice in another domain.
    – Doc Brown
    Sep 3, 2020 at 13:33

Another use case is for systems requiring that processing moves from state A to State B without forgetting about any update that happened between the read and the write.

By combining some date stamps (start, and last update, perhaps even end-date if you desire soft-deletion) a process can determine if a record has been updated since it was last read.

This allows optimistic locking, where instead of obtaining a lock initially. The process instead reads the data, performs its work, and then when it goes to update the record first checks that the guid, and timestamps match while updating the record. If it determines that the record has changed it can respond by redoing its own work, or be making a correction to its own processing and trying again.

This is particularly useful for:

  • Code running in parallel that might be competing to update the same record
  • A process that cannot maintain a transaction (and database write lock on the record) for the duration of it's processing.

One additional use case that I've run into a few times is when systems accept data from some canonical external source (for example: you maintain a list of books by ISBN and the data is potentially not complete, not accurate, or may be updated outside of your control in addition to inside your app). If you allow the user to import data or you sync against an API or repository, you may need to compare the last time data was changed inside the walls of your system ('updated_at') vs the last external import or sync event for that piece of data. An external sync with changed data may overwrite your existing data if the user hasn't updated it locally. A simple diff doesn't have enough fidelity to determine this.

There are other ways to ask "has this data been updated locally vs the last external sync", but other solutions propose things like adding flags to each record which are less informative or more comprehensive versioning / a complete audit trail which could put you afoul of data privacy laws or confidentiality agreements and require more complex implementations.


One use cases is for ETL (extract-transform-load) processes where you are processing data in bulk. You often want to know when the data was last upated or created the first time for audit purposes and data quality control:

CREATE TABLE etl_process_x (
    # more columns

By setting defaults and automatic updates, you don't need to deal with these attributes in queries. But when there is a flaw in the data it will help you track down if it was because of a recent update or something else.

Not the answer you're looking for? Browse other questions tagged or ask your own question.