CREATE TABLE audit_logs (
  log_id        BIGSERIAL PRIMARY KEY,
  event_epoch   BIGINT NOT NULL,
  recorded_at   TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  payload       JSONB
);

-- Index for range queries
CREATE INDEX idx_audit_logs_epoch ON audit_logs(event_epoch);

type EpochUnit = 'seconds' | 'milliseconds' | 'microseconds';

interface NormalizedEpoch {
  value: number;
  unit: EpochUnit;
}

function detectAndNormalize(rawInput: number | string): NormalizedEpoch {
  const numericValue = typeof rawInput === 'string' ? parseFloat(rawInput) : rawInput;
  
  if (isNaN(numericValue)) {
    throw new Error('Invalid epoch input: not a number');
  }

  const magnitude = Math.abs(numericValue);
  
  if (magnitude < 1e11) {
    return { value: numericValue, unit: 'seconds' };
  } else if (magnitude < 1e14) {
    return { value: numericValue / 1000, unit: 'seconds' };
  } else if (magnitude < 1e17) {
    return { value: numericValue / 1e6, unit: 'seconds' };
  }
  
  throw new Error('Epoch magnitude exceeds supported range');
}

import datetime
import zoneinfo

def serialize_epoch_t

o_iso(epoch_seconds: float, tz: str = "UTC") -> str: utc_dt = datetime.datetime.fromtimestamp(epoch_seconds, tz=datetime.timezone.utc) target_tz = zoneinfo.ZoneInfo(tz) localized_dt = utc_dt.astimezone(target_tz) return localized_dt.isoformat()

def deserialize_iso_to_epoch(iso_string: str) -> float: parsed_dt = datetime.datetime.fromisoformat(iso_string) if parsed_dt.tzinfo is None: parsed_dt = parsed_dt.replace(tzinfo=datetime.timezone.utc) return parsed_dt.timestamp()

**Rationale:** Explicit timezone handling during serialization prevents the "midnight UTC becomes yesterday evening" bug. Returning ISO strings with offsets (`+00:00`, `-04:00`) allows clients to render dates correctly without guessing the server's configuration.

### Step 4: Display Layer Conversion
Human-readable formatting belongs exclusively to the presentation tier. Pass normalized epoch values or ISO strings to the frontend, then apply timezone conversion using the user's locale or explicit preference.

**TypeScript Display Formatter:**
```typescript
function formatForDisplay(epochSec: number, userTz: string): string {
  const dateInstance = new Date(epochSec * 1000);
  return new Intl.DateTimeFormat('en-US', {
    timeZone: userTz,
    year: 'numeric',
    month: 'long',
    day: 'numeric',
    hour: '2-digit',
    minute: '2-digit',
    second: '2-digit',
    hour12: false
  }).format(dateInstance);
}

Rationale: Intl.DateTimeFormat leverages the ICU library for accurate DST and historical timezone rule application. Keeping conversion at the edge ensures backend services remain timezone-agnostic and horizontally scalable.

Pitfall Guide

1. The Millisecond/Second Multiplier Bug

Explanation: JavaScript's Date.now() and new Date().getTime() return milliseconds. Most backend languages, databases, and APIs expect seconds. Injecting a 13-digit millisecond value into a seconds-only pipeline shifts the date forward by ~33,000 years. Conversely, dividing seconds by 1000 when milliseconds are expected yields a date in early 1970. Fix: Implement magnitude-based validation at every ingestion boundary. Never trust client-provided units. Normalize to a single internal unit (preferably seconds) before storage or computation.

2. Implicit Server Timezone Leakage

Explanation: When a timestamp is converted to a date string without specifying a timezone, the runtime falls back to the server's local timezone. If the server is in Europe/Berlin but the user expects America/Los_Angeles, the displayed date shifts by 9 hours. This breaks audit trails, billing cycles, and compliance reporting. Fix: Always pass explicit timezone parameters to formatting functions. Store all timestamps in UTC. Convert to user timezone only at the presentation layer. Never rely on process.env.TZ or OS defaults in production.

3. Calendar Arithmetic on Raw Epochs

Explanation: Adding 86400 (seconds in a day) or 2592000 (seconds in 30 days) to an epoch value ignores leap seconds, DST transitions, and variable month lengths. Adding 30 days to January 31st yields March 2nd, not February 28th/29th. This breaks subscription billing, trial expirations, and scheduling systems. Fix: Use calendar-aware libraries (date-fns, dayjs, python-dateutil, or database native functions) for month/year arithmetic. Reserve raw epoch math for delta calculations, timeouts, and cache TTLs.

4. 32-Bit Integer Truncation (Y2K38)

Explanation: Signed 32-bit integers max out at 2,147,483,647, corresponding to January 19, 2038 at 03:14:07 UTC. Systems using INT columns or 32-bit language primitives will wrap to negative values, interpreting the timestamp as December 1901. This affects legacy embedded systems, older C/Java services, and misconfigured database schemas. Fix: Enforce BIGINT in databases, int64 in Go/Rust, and Number (which uses 64-bit floats) or BigInt in JavaScript. Audit CI/CD pipelines for 32-bit compilation flags. Plan migration before 2035 to avoid emergency patches.

5. Database Column Type Mismatch

Explanation: Using TIMESTAMP (without timezone) in PostgreSQL or DATETIME in MySQL stores the literal value provided, discarding timezone context. When the server timezone changes or the database is replicated across regions, queries return inconsistent results. Indexes on naive timestamps also fail to optimize cross-timezone range scans. Fix: Always use TIMESTAMPTZ in PostgreSQL or DATETIME with explicit UTC conversion in MySQL. Document the storage contract in schema migrations. Validate that ORM frameworks map to timezone-aware types by default.

6. API Payload Ambiguity

Explanation: Returning { "created_at": 1746835200 } forces clients to guess whether the value is seconds or milliseconds, and whether it represents UTC or local time. This leads to inconsistent parsing, duplicate conversion logic across frontend/backend, and silent data corruption when third-party integrations assume different units. Fix: Standardize on ISO 8601 with explicit offsets. Document the contract in OpenAPI specs. Reject raw epoch integers in public-facing endpoints. Use middleware to validate and transform payloads before they reach business logic.

Production Bundle

Action Checklist

• Audit all database columns storing time: replace naive types with TIMESTAMPTZ or BIGINT
• Implement magnitude-based epoch normalization at every API ingestion point
• Replace raw epoch arithmetic with calendar-aware libraries for billing/scheduling logic
• Enforce ISO 8601 serialization with explicit timezone offsets in all public APIs
• Configure frontend display layers to convert UTC epochs using Intl.DateTimeFormat
• Add integration tests that verify timezone conversion across UTC, EST, and PST
• Scan legacy services for 32-bit integer timestamp storage and plan migration to 64-bit
• Document the timestamp contract (unit, timezone, format) in architecture decision records

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High-frequency event logging	Raw 64-bit epoch seconds	Minimal storage, fast indexing, consistent arithmetic	Low storage, high query performance
User-facing SaaS application	TIMESTAMPTZ + ISO 8601 API	Handles DST/calendar math natively, unambiguous client parsing	Moderate storage, higher dev complexity
Legacy system migration	Epoch seconds with validation middleware	Preserves existing logic while enforcing unit safety	Low migration cost, requires thorough testing
Cross-region analytics pipeline	Native DB timestamp + UTC normalization	Leverages database partitioning and timezone-aware aggregation	Higher compute cost, accurate reporting
IoT/Embedded device telemetry	Raw milliseconds with explicit unit header	Matches hardware clock precision, reduces parsing overhead	Low bandwidth, requires strict client contracts

Configuration Template

TypeScript Boundary Middleware (Express/Fastify compatible):

import { Request, Response, NextFunction } from 'express';

export function enforceTimestampContract(req: Request, _res: Response, next: NextFunction) {
  const body = req.body;
  if (!body) return next();

  const timestampFields = ['created_at', 'updated_at', 'expires_at', 'scheduled_for'];
  
  for (const field of timestampFields) {
    if (body[field] !== undefined) {
      const raw = body[field];
      const normalized = normalizeEpoch(raw);
      body[field] = {
        epoch_seconds: normalized.value,
        iso_8601: new Date(normalized.value * 1000).toISOString(),
        unit: 'seconds'
      };
    }
  }
  
  next();
}

function normalizeEpoch(input: number | string): { value: number } {
  const num = typeof input === 'string' ? parseFloat(input) : input;
  if (isNaN(num)) throw new Error('Invalid timestamp format');
  
  const abs = Math.abs(num);
  if (abs < 1e11) return { value: num };
  if (abs < 1e14) return { value: num / 1000 };
  if (abs < 1e17) return { value: num / 1e6 };
  
  throw new Error('Timestamp magnitude out of range');
}

PostgreSQL Migration Snippet:

BEGIN;

ALTER TABLE user_sessions 
  ADD COLUMN created_at_tz TIMESTAMPTZ,
  ADD COLUMN expires_at_tz TIMESTAMPTZ;

UPDATE user_sessions 
SET 
  created_at_tz = to_timestamp(created_at_epoch),
  expires_at_tz = to_timestamp(expires_at_epoch);

ALTER TABLE user_sessions 
  DROP COLUMN created_at_epoch,
  DROP COLUMN expires_at_epoch,
  ALTER COLUMN created_at_tz SET NOT NULL,
  ALTER COLUMN expires_at_tz SET NOT NULL;

CREATE INDEX idx_sessions_expires ON user_sessions(expires_at_tz);

COMMIT;

Quick Start Guide

1. Define the contract: Decide on a single internal epoch unit (seconds recommended) and enforce it via validation middleware at every API entry point.
2. Update storage: Migrate database columns to TIMESTAMPTZ or BIGINT. Remove naive timestamp types and add explicit UTC defaults.
3. Standardize serialization: Replace raw epoch integers in API responses with ISO 8601 strings containing explicit timezone offsets. Document this in your OpenAPI specification.
4. Isolate display logic: Pass normalized epochs or ISO strings to the frontend. Use Intl.DateTimeFormat with user-provided timezone preferences for rendering. Never convert timezones in backend business logic.
5. Validate with tests: Write integration tests that inject milliseconds, seconds, and ISO strings, verifying that normalization, storage, and serialization behave identically across UTC, EST, and JST timezones.