Common Timestamp Bugs and How to Avoid Them

Timestamp bugs are uniquely frustrating — they're often intermittent (only at certain times of year or in certain timezones), hard to reproduce locally, and can cause real data loss. Here are the most common ones with fixes.

Connect on LinkedIn →

Bug 1: Seconds vs Milliseconds Confusion

Mixing seconds-based timestamps (Unix, Python, most databases) with milliseconds-based timestamps (JavaScript Date.now(), Java, MongoDB) causes dates that display as 1970 or 55,000 years in the future.

new Date(1740355200)        // ✗ Jan 20 1970
new Date(1740355200 * 1000) // ✓ Feb 24 2026

// Detect automatically:
const isMs = timestamp > 1e12; // 13 digits = milliseconds

Bug 2: Storing Local Time Instead of UTC

new Date().toString() and datetime.now() save the server's local time, which changes when the server is moved or DST kicks in. Always use UTC: new Date().toISOString(), datetime.now(timezone.utc), UTC_TIMESTAMP() in MySQL, NOW() AT TIME ZONE 'UTC' in PostgreSQL.

Bug 3: Daylight Saving Time Edge Cases

"Spring forward" makes one hour (2:00–3:00 AM) not exist — scheduled jobs targeting 2:30 AM don't fire. "Fall back" makes one hour happen twice — jobs may fire twice or log entries appear to go backwards. Fix: schedule in UTC, which has no DST.

Bug 4: Database Timezone Mismatches

MySQL's DATETIME type stores no timezone — it stores whatever you give it. If your app server is UTC but the database connection is configured to a different timezone, timestamps are silently shifted on read and write. Always set SET time_zone = '+00:00' in MySQL connections. Use PostgreSQL's TIMESTAMP WITH TIME ZONE which stores UTC correctly.

Bug 5: Off-By-One Day Errors

2026-02-24 22:00:00 PST is 2026-02-25 06:00:00 UTC. Reports grouped "by date" show different results depending on which timezone is used. Decide which timezone your business logic uses for "a day" and apply it consistently everywhere.

Bug 6: Comparing Timestamps of Different Precision

Comparing a seconds-precision database timestamp with a milliseconds-precision API value causes incorrect inequality comparisons. Normalise to the same unit before comparing.

Bug 7: Cache Keys With Date.now()

Date.now() changes every millisecond — using it directly in a cache key defeats the cache. Round to your desired window: Math.floor(Date.now() / 300000) * 300000 for 5-minute buckets.

Quick Reference: Safe Timestamp Practices

• Always store UTC, display in local timezone at render time
• Use 64-bit integers for timestamp storage
• Set database connection timezone to UTC explicitly
• Use ISO 8601 with Z suffix in JSON APIs
• Verify seconds vs milliseconds at every system boundary
• Schedule cron jobs in UTC

Why Timestamps Are Surprisingly Hard

Timestamps seem simple — they're just numbers representing points in time. But the complexity hides in the layers of abstraction between "what time is it" and "what number do I store." A Unix timestamp is seconds since January 1, 1970 UTC, but the same moment in time has a different representation in every time zone, and the offset between UTC and a local zone changes twice a year in most places due to daylight saving time.

This means that "March 10, 2024 at 2:30 AM" in the US Eastern time zone doesn't exist — clocks jumped from 2:00 AM directly to 3:00 AM. If your application allows a user to select that date and time, what do you store? The answer depends on your stack, your database, and whether you've thought about this edge case at all. Most applications haven't.

Timestamps in Databases: Storage vs Display

The single biggest source of timestamp bugs in production systems is confusion between storage format and display format. PostgreSQL's TIMESTAMP WITH TIME ZONE stores everything internally as UTC and converts on display. MySQL's TIMESTAMP type also converts to UTC for storage, but DATETIME stores the literal value without conversion. This means the same application code can produce different results depending on which column type was chosen, and the bug only appears when the application server and database server are in different time zones — which often means it works in development and breaks in production.

SQL Server's DATETIMEOFFSET stores both the UTC time and the original offset, which is the most information-preserving approach. If you need to know that a meeting was scheduled at "3 PM Tokyo time" rather than just the UTC equivalent, you need the offset. But this comes with its own complexity: two DATETIMEOFFSET values can represent the same instant but have different offsets, so equality comparisons must normalize to UTC first.

Testing Timestamp Code

Timestamp bugs are notoriously hard to test because they depend on wall-clock time, server configuration, and calendar rules. The most reliable approach is to make your clock injectable — wrap system time access behind an interface so tests can substitute a fixed or controlled clock. This lets you test DST transitions, year boundaries, leap seconds, and far-future dates without waiting for them to occur.

Pay special attention to testing around midnight UTC, around DST transitions in your target time zones, around the epoch (timestamp 0), and with timestamps that are milliseconds when your code expects seconds. The Date.now() function in JavaScript returns milliseconds, while most APIs and databases expect seconds — dividing by 1000 and accidentally truncating to an integer is one of the most common timestamp bugs in web applications.