created_at and updated_at: database DEFAULT or JPA annotation?
Almost every table has created_at and updated_at.
The problem isn't having them, it's not deciding who fills them: the
database (DEFAULT now()) or the application
(@CreationTimestamp/@UpdateTimestamp from Hibernate).
When both fill them, or neither, the familiar symptoms show up:
updated_at null forever, timestamps that diverge between rows
created by the API and rows created by a script, wrong time after someone
changed the server's timezone.
The two strategies
| DEFAULT in the database | Annotation in the application | |
|---|---|---|
| Works for manual INSERT, ETL, another service | yes | no |
| updated_at on UPDATE | needs a trigger | automatic |
| Value available on the entity before flush | no (needs a reread) | yes |
| Clock source | database server | each instance's JVM |
Both work. What doesn't work is mixing them without a rule. Rule of thumb: if
only the application writes to the table, the annotation handles it and the code
stays self-contained; if the table gets writes from outside (jobs, another
system, hand-written SQL), DEFAULT in the database plus a trigger for
updated_at is the only way to guarantee consistency.
timestamp vs timestamptz
In PostgreSQL, TIMESTAMP stores a "loose" time with no zone;
TIMESTAMPTZ normalizes to UTC on write. For an event instant
(created at, paid at), TIMESTAMPTZ is the right one: it survives a
server with a changed timezone and daylight saving. In Java, map it to
Instant or OffsetDateTime;
LocalDateTime pairs with TIMESTAMP and inherits the
same problems.
created_at TIMESTAMPTZ NOT NULL DEFAULT now()