Timestamp Drift & Default Reset

Learning Objectives

Understand how default timestamp values are set on INSERT and what can go wrong
Identify timestamp drift in high-write systems caused by clock skew
Test default values and NULL timestamp behavior across different database engines

Default Timestamp Values on INSERT

When a row is inserted without providing an explicit value for a timestamp column, the database fills it from its default. The two most common defaults are:

-- PostgreSQL
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()

-- MySQL
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

On the surface, these look identical. In practice, the timing and source of "now" depends on the database engine and the transaction context:

In PostgreSQL, NOW() returns the transaction start time, not the wall-clock time of the INSERT statement itself. If a transaction takes 5 minutes, NOW() at the end of the transaction returns the same value as at the start.
CURRENT_TIMESTAMP is a synonym for NOW() in PostgreSQL. CLOCK_TIMESTAMP() returns the actual wall-clock time at the moment of the call.

Test case: Begin a long-running transaction, insert a row at the start and another at the end. Verify whether created_at values differ. If both show the transaction start time, the system uses NOW() semantics — important for audit trails that need per-statement precision.

What Happens When a NULL Timestamp Column Gets Updated to NULL

Consider a nullable timestamp column:

last_login TIMESTAMPTZ NULL DEFAULT NULL

If the application issues UPDATE users SET last_login = NULL WHERE id = 1, the column is set to NULL. This seems obvious, but the bug appears in these scenarios:

Trigger reset: An updated_at trigger fires and sets updated_at = NOW() — even though the update was just clearing last_login. The user's updated_at now changes every time they log out.
MySQL implicit NOT NULL behavior: In MySQL, a TIMESTAMP column (not DATETIME) with no explicit NULL is implicitly NOT NULL. Attempting to insert NULL triggers MySQL to substitute the current timestamp — silently. This looks like the column "auto-populated" when the developer expected NULL.

Timestamp Drift in High-Write Systems

In systems that insert thousands of rows per second, timestamp columns can exhibit "drift" — the apparent ordering of rows by created_at does not match the logical insertion order.

Causes:

Connection pool reuse: Different connections use NOW() relative to their transaction start, not the insert order.
Clock skew between app servers: Application servers set created_at from System.currentTimeMillis() locally; if servers are not NTP-synchronized, timestamps from Server A can be earlier than Server B even when A's INSERT arrived later.
Batched writes with single timestamp: An ORM batches 500 inserts in one transaction; all 500 records get the same created_at.

Test case for drift: Insert 100 records rapidly and query them ordered by created_at ASC. Compare to the order returned by id ASC (serial primary key). If created_at order differs from id order, drift is present.

Testing Default Values: Checklist

Test	What to Verify
INSERT without created_at	created_at is set to a recent timestamp (within 1s of test run)
INSERT without updated_at	updated_at equals created_at on new row
INSERT with explicit NULL	Column stores NULL if nullable; error or substitution if NOT NULL
INSERT in a long transaction	created_at reflects transaction start, not commit time
Clock skew simulation	Temporarily offset app server time; verify DB timestamps are consistent
Duplicate inserts (idempotency)	A second insert with the same natural key either fails or leaves created_at unchanged