Timestamps in APIs, Databases, and Logs

Production debugging often means aligning events from an HTTP trace, a database row, and three log files—each with its own time representation.

A correlation workflow

1. Pick a reference instant — e.g. user report time or request ID timestamp
2. Normalize everything to UTC epoch ms (or ns if you have it)
3. Apply clock skew tolerance — laptops and containers drift; ±2 minutes is common in loose systems
4. Filter adjacent window — reference ± 5 minutes before deep grep
5. Document the source unit in your notes (nginx: sec, app: ISO UTC, db: timestamptz)

APIs

REST JSON might expose:

{ "createdAt": 1700000000000, "updatedAt": "2023-11-14T22:13:20.000Z" }

Mixed types in one payload happen when services evolve. Parse each field on its own terms.

GraphQL and protobuf schemas usually document scalar types—read them before writing client comparisons.

Databases

• PostgreSQL TIMESTAMPTZ stores UTC internally—good default
• BIGINT epoch columns require you to remember seconds vs ms forever
• DATETIME without timezone (MySQL legacy) stores “wall time as given”—dangerous for global apps

When joining app logs to SQL, convert both sides to the same instant representation first.

Distributed logs

Use trace IDs that embed or accompany timestamps. If only wall-clock strings exist, convert a sample line from each service using a known event (deploy marker, heartbeat) to estimate skew.

Key takeaway

Cross-service debugging is normalization + windowed search, not guessing local clocks. Convert first, compare second, widen the window third.