Timezone Bugs in Distributed Systems — When 9 AM Means Different Things

// ❌ Storing local times in the database
await db.query(`
  INSERT INTO appointments (user_id, scheduled_at, timezone)
  VALUES (?, '2026-03-15 14:00:00', 'America/New_York')
`)
// What does 14:00:00 mean? Local time? UTC? Which timezone?
// Ambiguous, unordered, ungroupable by time

// ✅ Store UTC timestamps — convert to local only for display
import { toZonedTime, fromZonedTime } from 'date-fns-tz'

async function scheduleAppointment(
  userId: string,
  localTime: string,        // '2026-03-15 14:00:00'
  userTimezone: string      // 'America/New_York'
) {
  // Convert user's local time to UTC for storage
  const utcTime = fromZonedTime(new Date(localTime), userTimezone)

  await db.query(`
    INSERT INTO appointments (user_id, scheduled_at_utc, user_timezone)
    VALUES (?, ?, ?)
  `, [userId, utcTime, userTimezone])
}

// Display: convert back to user's timezone
async function getAppointment(appointmentId: string, displayTimezone: string) {
  const appt = await db.query('SELECT * FROM appointments WHERE id = ?', [appointmentId])

  return {
    ...appt,
    displayTime: toZonedTime(appt.scheduled_at_utc, displayTimezone)
      .toLocaleString('en-US', { timeZone: displayTimezone }),
  }
}

// ❌ Parsing without timezone — depends on server locale
const date = new Date('2026-03-15')
// Interpreted as midnight UTC? Midnight local? Depends on environment!

const date2 = new Date('2026-03-15T14:00:00')
// Interpreted as local time — wrong on UTC servers!

// ✅ Always be explicit about timezone
import { parseISO } from 'date-fns'
import { fromZonedTime } from 'date-fns-tz'

// If you know the timezone of the input
const utcDate = fromZonedTime('2026-03-15T14:00:00', 'America/New_York')

// For API inputs: require timezone offset in ISO 8601
const iso = '2026-03-15T14:00:00-05:00'  // East Coast (EST)
const utcFromIso = parseISO(iso)            // Always parses correctly

// Validate ISO 8601 with timezone from API clients
function parseUserTimestamp(input: string): Date {
  if (!input.match(/[+-]\d{2}:\d{2}$|Z$/)) {
    throw new Error('Timestamp must include timezone offset (e.g., 2026-03-15T14:00:00-05:00)')
  }
  return parseISO(input)
}

-- PostgreSQL: use TIMESTAMPTZ (timestamp with time zone) not TIMESTAMP
-- TIMESTAMPTZ stores UTC internally, converts on display

-- ❌ TIMESTAMP — stores whatever you give it, no timezone conversion
CREATE TABLE events (
  created_at TIMESTAMP  -- Ambiguous! Is this UTC or local?
);

-- ✅ TIMESTAMPTZ — always stored as UTC, timezone-aware queries
CREATE TABLE events (
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Setting session timezone for display
SET timezone = 'America/New_York';
SELECT created_at FROM events;  -- Now displayed in EST

-- Range queries work correctly with TIMESTAMPTZ
SELECT * FROM events
WHERE created_at >= '2026-03-15 00:00:00-05:00'  -- Midnight Eastern
  AND created_at < '2026-03-16 00:00:00-05:00';

// Node.js: set PostgreSQL client timezone to UTC
import { Pool } from 'pg'

const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
})

// Ensure all connections use UTC
pool.on('connect', (client) => {
  client.query("SET timezone = 'UTC'")
})

// Also set Node.js timezone (important for date formatting)
// process.env.TZ = 'UTC'  // Set at app startup
// Or: TZ=UTC node server.js

// ❌ Cron runs based on server timezone — could be UTC, could be anything
import cron from 'node-cron'

cron.schedule('0 9 * * *', handler)  // "9 AM" — but where?!

// ✅ Use UTC for server-side cron — calculate the right UTC time
// "Run at 9 AM New York time" = "14:00 UTC in winter, 13:00 UTC in summer"
// DST makes this tricky — use timezone-aware libraries

import { CronJob } from 'cron'

// cron.js supports timezone natively
const job = new CronJob(
  '0 9 * * *',
  handler,
  null,
  true,
  'America/New_York'  // Timezone-aware — handles DST automatically
)

// For cloud cron (AWS EventBridge, GCP Cloud Scheduler):
// Always schedule in UTC, or use the timezone parameter if available

// ❌ Common mistake: "today's orders" uses server timezone

async function getTodaysOrders() {
  const today = new Date()
  today.setHours(0, 0, 0, 0)   // Midnight SERVER TIME (might be UTC!)
  const tomorrow = new Date(today)
  tomorrow.setDate(tomorrow.getDate() + 1)

  return db.order.findAll({
    where: { createdAt: { $gte: today, $lt: tomorrow } }
  })
  // User in Tokyo sees "today" as UTC day — might span 2 calendar days
}

// ✅ Always specify timezone for date range calculations
import { startOfDay, endOfDay } from 'date-fns'
import { toZonedTime, fromZonedTime } from 'date-fns-tz'

async function getTodaysOrdersForTimezone(userTimezone: string) {
  const now = new Date()
  const userNow = toZonedTime(now, userTimezone)

  const startOfUserDay = fromZonedTime(startOfDay(userNow), userTimezone)
  const endOfUserDay = fromZonedTime(endOfDay(userNow), userTimezone)

  return db.order.findAll({
    where: {
      createdAt: {
        $gte: startOfUserDay,  // UTC equivalent of 00:00 in user's tz
        $lte: endOfUserDay,    // UTC equivalent of 23:59 in user's tz
      }
    }
  })
}

// ❌ Sending JavaScript Date object — serializes to local server time string
res.json({ createdAt: new Date() })
// → {"createdAt": "2026-03-15T09:00:00.000Z"}  ✅ (this is actually fine)
// But: if Date was created incorrectly, it propagates the bug

// ❌ Sending a formatted local time string
res.json({ createdAt: new Date().toLocaleDateString() })
// → {"createdAt": "3/15/2026"}  — which timezone? Unusable!

// ✅ Always serialize as ISO 8601 UTC
res.json({
  createdAt: new Date().toISOString(),  // Always UTC with Z suffix
  // → "2026-03-15T14:00:00.000Z"
})

// ✅ Or include timezone offset
res.json({
  createdAt: {
    utc: new Date().toISOString(),
    userLocal: toZonedTime(new Date(), user.timezone).toISOString(),
    timezone: user.timezone,
  }
})