Timezone Handling in Database Timestamps: Best Practices

The Timezone Problem in Databases

Timezone mishandling is one of the most common sources of bugs in web applications. Appointments show up on the wrong day. Daily reports include data from the previous day. Scheduled tasks fire at unexpected hours. The root cause is almost always inconsistent timezone handling between the application layer and the database.

The solution is straightforward in principle: store timestamps in UTC, and convert to local time only at the display layer. In practice, the details matter.

Storage Strategies

Strategy 1: Always Store UTC

This is the most widely recommended approach. Every timestamp enters the database as UTC, and the application converts to the user's local timezone at render time.

-- PostgreSQL: TIMESTAMP WITHOUT TIME ZONE storing UTC values
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL  -- always UTC, even though the column doesn't enforce it
);

-- Insert as UTC
INSERT INTO events (name, created_at)
VALUES ('User signup', '2024-05-28 14:30:00');

Risk: The database does not enforce UTC. A developer can insert local time by mistake, and nothing catches the error until a user reports wrong timestamps.

Strategy 2: Use TIMESTAMPTZ

PostgreSQL's TIMESTAMPTZ column type stores the timestamp converted to UTC internally but remembers the timezone offset for input:

CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL  -- stores UTC internally
);

-- Input with offset — PostgreSQL converts to UTC automatically
INSERT INTO events (name, created_at)
VALUES ('Meeting', '2024-05-28 14:30:00+02:00');
-- Stored internally as 2024-05-28 12:30:00 UTC

When you query a TIMESTAMPTZ column, PostgreSQL returns the value in the session's timezone setting:

-- Set session timezone
SET timezone = 'America/New_York';

SELECT created_at FROM events;
-- Returns: 2024-05-28 08:30:00-04:00

Comparison

Application Layer Handling

Sending Timestamps to the Database

Always send timestamps in ISO 8601 format with UTC indicator:

// Node.js — always send UTC to the database
const now = new Date();                           // local time object
const utcISO = now.toISOString();                 // "2024-05-28T14:30:00.000Z"
const epochSeconds = Math.floor(now.getTime() / 1000); // 1716898200

// With an ORM like Prisma
await prisma.event.create({
  data: {
    name: 'User signup',
    createdAt: now,  // Prisma handles UTC conversion
  },
});

Reading Timestamps from the Database

Convert to the user's local timezone only at the presentation layer:

// Server: return UTC ISO string to client
app.get('/events', async (req, res) => {
  const events = await db.query('SELECT * FROM events');
  // Send UTC timestamps — the client converts to local time
  res.json(events);
});

// Client: display in user's timezone
function formatEventTime(utcISO) {
  return new Date(utcISO).toLocaleString('en-US', {
    timeZone: Intl.DateTimeFormat().resolvedOptions().timeZone,
    dateStyle: 'medium',
    timeStyle: 'short',
  });
}

Timezone-Aware Queries

Filtering by "Today" in a User's Timezone

A common query is "show me all events from today." If your database stores UTC, you must compute the UTC boundaries for the user's local day:

-- Events on May 28, 2024 in America/New_York (UTC-4)
SELECT * FROM events
WHERE created_at >= '2024-05-28 04:00:00'  -- midnight EDT = 04:00 UTC
  AND created_at <  '2024-05-29 04:00:00'; -- next midnight EDT

// Compute UTC boundaries in application code
function getDayBoundaries(date, timezone) {
  const start = new Date(Date.UTC(date.getFullYear(), date.getMonth(), date.getDate()));
  const utcStart = new Date(start.toLocaleString('en-US', { timeZone: timezone }));
  const offset = start - utcStart; // timezone offset in ms
  return {
    start: new Date(start.getTime() + offset),
    end: new Date(start.getTime() + offset + 86400000),
  };
}

DST Transitions

Daylight Saving Time complicates offset calculations. A timezone like America/New_York shifts between UTC-5 and UTC-4. Always use a timezone library (like luxon, date-fns-tz, or moment-timezone) rather than hardcoding offsets:

const { DateTime } = require('luxon');

// Correct: library handles DST
const localTime = DateTime.fromISO('2024-03-10T02:30:00', { zone: 'America/New_York' });
// This time doesn't exist — clocks jump from 2:00 to 3:00 AM
// Luxon handles it by default (adjusts forward)

Common Mistakes

Storing Local Time Without Timezone

-- Danger: no timezone info — ambiguous during DST transitions
INSERT INTO events (created_at) VALUES ('2024-07-15 14:30:00');
-- Is this 14:30 EDT or 14:30 UTC? The database doesn't know.

Using CURRENT_TIMESTAMP Without Understanding It

In PostgreSQL, CURRENT_TIMESTAMP returns the current time in the session's timezone. If the session timezone is misconfigured, your inserts silently use the wrong offset.

Comparing TIMESTAMP and TIMESTAMPTZ Columns

Mixing these column types in queries produces confusing results because PostgreSQL silently converts between them using the session timezone.

Key Takeaways

• Store all timestamps in UTC — either as TIMESTAMPTZ, ISO 8601 strings with Z, or Unix epoch integers
• Convert to local time only at the display layer, never in the database or API layer
• Use TIMESTAMPTZ in PostgreSQL for automatic UTC conversion on input
• Compute timezone boundaries in application code when filtering by "today" or date ranges
• Always use a timezone library for DST-aware calculations rather than hardcoded offsets
• Never store local time without an offset — it becomes ambiguous during DST transitions

Try It Yourself

Convert timestamps between UTC, local time, and different timezone formats with our free Timestamp Converter. Supports ISO 8601, Unix epoch, and custom formats — all processed locally in your browser.

Try the Timestamp Converter →