Database Timestamp Storage: Best Practices

Let's be real: how you store timestamps in your database matters. Like, really matters. Get it wrong and you're looking at timezone bugs that only appear for users in certain regions, data corruption that's a nightmare to debug, queries that grind to a halt, and a schema that becomes impossible to maintain.

I've seen production systems brought down by bad timestamp storage. Don't let yours be one of them. This guide will walk you through the right way to store timestamps across SQL and NoSQL databases, with real examples you can actually use.

The Golden Rules

Rule 1: Always Store UTC

-- ❌ WRONG: Storing local time without timezone
CREATE TABLE events (
  id BIGINT PRIMARY KEY,
  event_time TIMESTAMP  -- Ambiguous! Which timezone?
);

-- ✅ CORRECT: Store UTC with timezone awareness
CREATE TABLE events (
  id BIGINT PRIMARY KEY,
  event_time TIMESTAMP WITH TIME ZONE,  -- PostgreSQL
  user_timezone VARCHAR(50)  -- Store timezone separately if needed
);

Rule 2: Choose the Right Data Type

For SQL Databases:

• TIMESTAMP WITH TIME ZONE (PostgreSQL)
• DATETIME or Unix BIGINT (MySQL)
• DATETIMEOFFSET (SQL Server)

For NoSQL Databases:

• Date object (MongoDB - stores as UTC milliseconds)
• Unix timestamp number (Firebase, DynamoDB)

Rule 3: Index Timestamp Columns

-- Essential for time-range queries
CREATE INDEX idx_events_time ON events(event_time);

-- For queries filtering by time + other columns
CREATE INDEX idx_events_user_time ON events(user_id, event_time);

PostgreSQL: The Gold Standard

Best Data Types

TIMESTAMP WITH TIME ZONE (Recommended)

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,

  -- Stores UTC internally, converts on retrieval
  occurred_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

  -- User's intended timezone for display
  user_timezone VARCHAR(50) NOT NULL DEFAULT 'UTC',

  created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW()
);

-- Insert (automatically converts to UTC)
INSERT INTO events (name, occurred_at, user_timezone)
VALUES ('User Login', '2024-01-15 14:30:00-05:00', 'America/New_York');

-- Query (automatically converts from UTC)
SELECT
  name,
  occurred_at,  -- Returns in session timezone
  occurred_at AT TIME ZONE 'America/New_York' AS ny_time,
  occurred_at AT TIME ZONE 'UTC' AS utc_time
FROM events;

Unix Timestamp (Alternative)

CREATE TABLE events (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,

  -- Store as Unix seconds (UTC)
  occurred_at_unix BIGINT NOT NULL,

  -- Computed column for convenience
  occurred_at TIMESTAMP WITH TIME ZONE
    GENERATED ALWAYS AS (TO_TIMESTAMP(occurred_at_unix)) STORED
);

-- Insert
INSERT INTO events (name, occurred_at_unix)
VALUES ('API Call', EXTRACT(EPOCH FROM NOW()));

-- Query
SELECT
  name,
  TO_TIMESTAMP(occurred_at_unix) AS timestamp,
  occurred_at  -- Computed column
FROM events
WHERE occurred_at_unix > EXTRACT(EPOCH FROM NOW() - INTERVAL '1 day');

Timezone Conversion

-- Convert between timezones
SELECT
  occurred_at,
  occurred_at AT TIME ZONE 'America/New_York' AS ny_time,
  occurred_at AT TIME ZONE 'Europe/London' AS london_time,
  occurred_at AT TIME ZONE 'Asia/Tokyo' AS tokyo_time
FROM events
WHERE occurred_at > NOW() - INTERVAL '1 day';

-- Current time in different timezones
SELECT
  NOW() AS server_time,
  NOW() AT TIME ZONE 'UTC' AS utc_time,
  NOW() AT TIME ZONE 'America/New_York' AS ny_time;

Indexing Strategies

-- Basic time index
CREATE INDEX idx_events_occurred ON events(occurred_at);

-- Partial index for recent data (PostgreSQL)
CREATE INDEX idx_events_recent
  ON events(occurred_at)
  WHERE occurred_at > NOW() - INTERVAL '30 days';

-- Index for time-range queries with other filters
CREATE INDEX idx_events_user_time ON events(user_id, occurred_at);

-- Index for grouping by date
CREATE INDEX idx_events_date ON events(DATE(occurred_at));

-- B-tree vs BRIN indexes
CREATE INDEX idx_events_brin ON events USING BRIN(occurred_at);
-- BRIN: Better for large, append-only tables with sequential timestamps

Query Optimization

-- ✅ GOOD: Index-friendly time range
SELECT * FROM events
WHERE occurred_at >= '2024-01-01'::TIMESTAMP
  AND occurred_at < '2024-02-01'::TIMESTAMP;

-- ❌ BAD: Function on column prevents index usage
SELECT * FROM events
WHERE DATE(occurred_at) = '2024-01-15';  -- Can't use index!

-- ✅ GOOD: Rewrite to use index
SELECT * FROM events
WHERE occurred_at >= '2024-01-15'::TIMESTAMP
  AND occurred_at < '2024-01-16'::TIMESTAMP;

-- Materialized views for aggregations
CREATE MATERIALIZED VIEW daily_events AS
SELECT
  DATE(occurred_at) AS event_date,
  COUNT(*) AS event_count,
  MIN(occurred_at) AS first_event,
  MAX(occurred_at) AS last_event
FROM events
GROUP BY DATE(occurred_at);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_events;

MySQL: Practical Approaches

Data Types

DATETIME (Recommended for simplicity)

CREATE TABLE events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,

  -- Stores literal value (no timezone conversion)
  -- Always store UTC, convert in application
  occurred_at DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP()),

  -- Store timezone separately
  user_timezone VARCHAR(50) NOT NULL DEFAULT 'UTC',

  created_at DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP()),

  INDEX idx_occurred(occurred_at)
) ENGINE=InnoDB;

-- Insert UTC time
INSERT INTO events (name, occurred_at)
VALUES ('Event Name', UTC_TIMESTAMP());

-- Query time range
SELECT * FROM events
WHERE occurred_at >= '2024-01-01 00:00:00'
  AND occurred_at < '2024-02-01 00:00:00';

TIMESTAMP (MySQL-specific limitations)

-- MySQL TIMESTAMP limitations:
-- 1. Only range: 1970-01-01 to 2038-01-19 (Y2038 problem!)
-- 2. Automatically converts between server timezone and UTC
-- 3. Limited to seconds precision

CREATE TABLE events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255),

  -- USE WITH CAUTION: Subject to Y2038 problem
  occurred_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

  INDEX idx_occurred(occurred_at)
);

-- Better: Use DATETIME or BIGINT for Unix timestamps

Unix Timestamp (Recommended for portability)

CREATE TABLE events (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,

  -- Unix seconds (UTC)
  occurred_at_unix BIGINT NOT NULL,

  -- Virtual column for queries (MySQL 5.7+)
  occurred_at DATETIME AS (FROM_UNIXTIME(occurred_at_unix)),

  INDEX idx_occurred(occurred_at_unix)
);

-- Insert
INSERT INTO events (name, occurred_at_unix)
VALUES ('Event', UNIX_TIMESTAMP());

-- Query with conversion
SELECT
  name,
  FROM_UNIXTIME(occurred_at_unix) AS occurred_at,
  occurred_at  -- Virtual column
FROM events
WHERE occurred_at_unix > UNIX_TIMESTAMP() - 86400;

Timezone Handling in MySQL

-- Set session timezone
SET time_zone = '+00:00';  -- UTC

-- Convert between timezones (requires timezone tables)
SELECT
  occurred_at,
  CONVERT_TZ(occurred_at, 'UTC', 'America/New_York') AS ny_time
FROM events;

-- Load timezone tables (one-time setup)
-- mysql_tzinfo_to_sql /usr/share/zoneinfo | mysql -u root mysql

Partitioning by Time

-- Range partitioning for time-series data
CREATE TABLE events (
  id BIGINT AUTO_INCREMENT,
  name VARCHAR(255),
  occurred_at DATETIME NOT NULL,
  PRIMARY KEY (id, occurred_at)
)
PARTITION BY RANGE (YEAR(occurred_at)) (
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN (2025),
  PARTITION p2025 VALUES LESS THAN (2026),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

-- Drop old partitions efficiently
ALTER TABLE events DROP PARTITION p2023;

SQL Server: Enterprise Features

DATETIMEOFFSET (Recommended)

CREATE TABLE Events (
  Id BIGINT IDENTITY PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL,

  -- Stores UTC time + offset
  OccurredAt DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),

  -- User's timezone for display
  UserTimezone NVARCHAR(50) NOT NULL DEFAULT 'UTC',

  CreatedAt DATETIMEOFFSET NOT NULL DEFAULT SYSDATETIMEOFFSET(),

  INDEX IX_Events_OccurredAt (OccurredAt)
);

-- Insert
INSERT INTO Events (Name, OccurredAt)
VALUES ('Event', SYSDATETIMEOFFSET());

-- Convert to different timezone
SELECT
  Name,
  OccurredAt,
  OccurredAt AT TIME ZONE 'Eastern Standard Time' AS EasternTime,
  CAST(OccurredAt AT TIME ZONE 'UTC' AS DATETIME) AS UtcTime
FROM Events;

Temporal Tables (System-Versioning)

-- Automatic change tracking with timestamps
CREATE TABLE Events (
  Id BIGINT IDENTITY PRIMARY KEY,
  Name NVARCHAR(255) NOT NULL,
  OccurredAt DATETIMEOFFSET NOT NULL,

  -- System-versioned columns
  ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START NOT NULL,
  ValidTo DATETIME2 GENERATED ALWAYS AS ROW END NOT NULL,
  PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EventsHistory));

-- Query historical data
SELECT * FROM Events
FOR SYSTEM_TIME AS OF '2024-01-15T00:00:00';

-- Query all versions
SELECT * FROM Events
FOR SYSTEM_TIME ALL
WHERE Id = 123;

MongoDB: Document-Oriented Approach

Date Storage

// MongoDB stores dates as UTC milliseconds (Unix milliseconds)
db.events.insertOne({
  _id: ObjectId(),
  name: 'User Login',

  // Stored as Date object (UTC milliseconds internally)
  occurredAt: new Date('2024-01-15T19:00:00Z'),

  // Store timezone separately
  userTimezone: 'America/New_York',

  createdAt: new Date(),
  updatedAt: new Date()
});

// Query time range
db.events.find({
  occurredAt: {
    $gte: new Date('2024-01-01T00:00:00Z'),
    $lt: new Date('2024-02-01T00:00:00Z')
  }
});

// Aggregation with date operations
db.events.aggregate([
  {
    $match: {
      occurredAt: { $gte: new Date('2024-01-01T00:00:00Z') }
    }
  },
  {
    $group: {
      _id: { $dateToString: { format: '%Y-%m-%d', date: '$occurredAt' } },
      count: { $sum: 1 },
      firstEvent: { $min: '$occurredAt' },
      lastEvent: { $max: '$occurredAt' }
    }
  }
]);

Indexing

// Create index on timestamp field
db.events.createIndex({ occurredAt: 1 });

// Compound index for filtered time queries
db.events.createIndex({ userId: 1, occurredAt: -1 });

// TTL index for automatic deletion
db.events.createIndex(
  { createdAt: 1 },
  { expireAfterSeconds: 2592000 }  // 30 days
);

Schema Design Patterns

// Pattern 1: Embedded timezone data
{
  _id: ObjectId(),
  event: 'Meeting',
  timestamp: {
    utc: new Date('2024-01-15T19:00:00Z'),
    timezone: 'America/New_York',
    localTime: '2024-01-15T14:00:00-05:00',
    offset: -300  // Minutes from UTC
  }
}

// Pattern 2: Store Unix timestamp + timezone
{
  _id: ObjectId(),
  event: 'Meeting',
  occurredAtUnix: 1705341600,  // Unix seconds
  timezone: 'America/New_York'
}

// Pattern 3: Multiple timestamp formats
{
  _id: ObjectId(),
  event: 'API Call',
  timestamps: {
    unix: 1705341600,
    iso: '2024-01-15T19:00:00.000Z',
    date: new Date('2024-01-15T19:00:00Z')
  },
  timezone: 'UTC'
}

Schema Design Patterns

Pattern 1: Separate Timezone Column

CREATE TABLE scheduled_events (
  id BIGINT PRIMARY KEY,
  title VARCHAR(255),

  -- Always UTC
  scheduled_at TIMESTAMP WITH TIME ZONE NOT NULL,

  -- User's intended timezone
  user_timezone VARCHAR(50) NOT NULL,

  -- Optional: Store intended local time for DST handling
  intended_local_time TIME
);

-- Insert
INSERT INTO scheduled_events (title, scheduled_at, user_timezone)
VALUES (
  'Team Meeting',
  '2024-03-10T14:00:00Z',
  'America/New_York'
);

-- Query: Get user's local time
SELECT
  title,
  scheduled_at AT TIME ZONE user_timezone AS local_time,
  user_timezone
FROM scheduled_events
WHERE user_timezone = 'America/New_York';

Pattern 2: Event Sourcing

CREATE TABLE event_log (
  event_id BIGSERIAL PRIMARY KEY,
  aggregate_id UUID NOT NULL,
  event_type VARCHAR(100) NOT NULL,
  event_data JSONB NOT NULL,

  -- Event timestamp (when event occurred in domain)
  occurred_at TIMESTAMP WITH TIME ZONE NOT NULL,

  -- System timestamp (when event was recorded)
  recorded_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT NOW(),

  INDEX idx_aggregate (aggregate_id, occurred_at),
  INDEX idx_recorded (recorded_at)
);

Pattern 3: Temporal Range

CREATE TABLE reservations (
  id BIGINT PRIMARY KEY,
  resource_id BIGINT NOT NULL,
  user_id BIGINT NOT NULL,

  -- Time range
  start_time TIMESTAMP WITH TIME ZONE NOT NULL,
  end_time TIMESTAMP WITH TIME ZONE NOT NULL,

  -- Range constraint
  CONSTRAINT valid_range CHECK (start_time < end_time),

  -- Prevent overlaps (PostgreSQL exclusion constraint)
  EXCLUDE USING GIST (
    resource_id WITH =,
    tstzrange(start_time, end_time) WITH &&
  )
);

Migration Strategies

Strategy 1: Add New Column, Backfill, Swap

-- Step 1: Add new UTC column
ALTER TABLE events
ADD COLUMN occurred_at_utc TIMESTAMP WITH TIME ZONE;

-- Step 2: Backfill data (in batches)
UPDATE events
SET occurred_at_utc = occurred_at AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC'
WHERE occurred_at_utc IS NULL
LIMIT 10000;

-- Step 3: Make non-nullable
ALTER TABLE events
ALTER COLUMN occurred_at_utc SET NOT NULL;

-- Step 4: Create index
CREATE INDEX CONCURRENTLY idx_events_utc ON events(occurred_at_utc);

-- Step 5: Update application to use new column
-- (Deploy application changes)

-- Step 6: Drop old column
ALTER TABLE events DROP COLUMN occurred_at;

-- Step 7: Rename new column
ALTER TABLE events RENAME COLUMN occurred_at_utc TO occurred_at;

Strategy 2: Shadow Table

-- Create new table with correct schema
CREATE TABLE events_new (
  id BIGSERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  occurred_at TIMESTAMP WITH TIME ZONE NOT NULL,
  user_timezone VARCHAR(50) NOT NULL
);

-- Copy data with transformation
INSERT INTO events_new (id, name, occurred_at, user_timezone)
SELECT
  id,
  name,
  occurred_at AT TIME ZONE 'America/New_York' AT TIME ZONE 'UTC',
  'America/New_York'
FROM events;

-- Atomic swap (use transaction + locks)
BEGIN;
ALTER TABLE events RENAME TO events_old;
ALTER TABLE events_new RENAME TO events;
COMMIT;

-- Verify and drop old table
DROP TABLE events_old;

Strategy 3: Dual-Write Period

// Phase 1: Write to both columns
async function createEvent(event) {
  await db.query(`
    INSERT INTO events (name, occurred_at, occurred_at_utc, user_timezone)
    VALUES ($1, $2, $3, $4)
  `, [
    event.name,
    event.localTime,  // Old column
    event.utcTime,    // New column
    event.timezone
  ]);
}

// Phase 2: Read from new column, fallback to old
async function getEvent(id) {
  const row = await db.query('SELECT * FROM events WHERE id = $1', [id]);
  return {
    ...row,
    occurredAt: row.occurred_at_utc || convertToUTC(row.occurred_at, row.user_timezone)
  };
}

// Phase 3: Drop old column (after full migration)

Performance Optimization

Partitioning

-- PostgreSQL declarative partitioning
CREATE TABLE events (
  id BIGSERIAL,
  name VARCHAR(255),
  occurred_at TIMESTAMP WITH TIME ZONE NOT NULL,
  PRIMARY KEY (id, occurred_at)
) PARTITION BY RANGE (occurred_at);

-- Create partitions
CREATE TABLE events_2024_01 PARTITION OF events
  FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE events_2024_02 PARTITION OF events
  FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Automatic partition pruning for queries
SELECT * FROM events
WHERE occurred_at >= '2024-01-15' AND occurred_at < '2024-01-20';
-- Only scans events_2024_01 partition

Compression

-- PostgreSQL: Use TOAST compression for large timestamp columns
ALTER TABLE events ALTER COLUMN event_data SET STORAGE EXTENDED;

-- Time-series compression (TimescaleDB)
SELECT create_hypertable('events', 'occurred_at');
ALTER TABLE events SET (
  timescaledb.compress,
  timescaledb.compress_segmentby = 'user_id'
);

Caching Strategies

// Cache aggregated time-range queries
const Redis = require('redis');
const redis = Redis.createClient();

async function getDailyEventCount(date) {
  const cacheKey = `events:count:${date}`;

  // Check cache
  const cached = await redis.get(cacheKey);
  if (cached) return JSON.parse(cached);

  // Query database
  const result = await db.query(`
    SELECT COUNT(*) as count
    FROM events
    WHERE DATE(occurred_at) = $1
  `, [date]);

  // Cache for 1 hour
  await redis.setex(cacheKey, 3600, JSON.stringify(result));

  return result;
}

Best Practices Checklist

Schema Design

• [ ] Use TIMESTAMP WITH TIME ZONE or equivalent
• [ ] Always store UTC timestamps
• [ ] Store user timezone separately when needed
• [ ] Use BIGINT for Unix timestamps to avoid Y2038
• [ ] Add check constraints for valid ranges
• [ ] Use appropriate precision (seconds vs milliseconds)

Indexing

• [ ] Index timestamp columns used in WHERE clauses
• [ ] Create compound indexes for filtered time queries
• [ ] Use partial indexes for recent data (PostgreSQL)
• [ ] Consider BRIN indexes for append-only data
• [ ] Monitor index usage and remove unused indexes

Queries

• [ ] Avoid functions on indexed columns
• [ ] Use time-range queries instead of exact matches
• [ ] Batch updates and inserts
• [ ] Use materialized views for aggregations
• [ ] Consider partitioning for large time-series data

Application Layer

• [ ] Convert to UTC before INSERT/UPDATE
• [ ] Convert to local timezone on SELECT (in app, not DB)
• [ ] Validate timestamps before storage
• [ ] Handle DST transitions correctly
• [ ] Use timezone libraries (not manual calculations)

Operations

• [ ] Regular backups with point-in-time recovery
• [ ] Monitor query performance on timestamp columns
• [ ] Update timezone data regularly (IANA database)
• [ ] Test migrations thoroughly
• [ ] Document timezone handling in schema

Troubleshooting Common Issues

Issue 1: Slow Time-Range Queries

-- Problem: Function prevents index usage
EXPLAIN ANALYZE
SELECT * FROM events
WHERE DATE(occurred_at) = '2024-01-15';

-- Solution: Rewrite to use index
EXPLAIN ANALYZE
SELECT * FROM events
WHERE occurred_at >= '2024-01-15'::TIMESTAMP
  AND occurred_at < '2024-01-16'::TIMESTAMP;

Issue 2: Timezone Conversion Overhead

-- Problem: Converting every row
SELECT
  id,
  occurred_at AT TIME ZONE 'America/New_York' AS local_time
FROM events
LIMIT 1000000;  -- Slow!

-- Solution: Convert in application layer after fetching
SELECT id, occurred_at
FROM events
LIMIT 1000000;
-- Convert to local timezone in application code

Issue 3: Y2038 Problem

-- Check if using vulnerable data type
SHOW CREATE TABLE events;

-- If using TIMESTAMP (32-bit), migrate to:
-- - DATETIME (MySQL)
-- - BIGINT for Unix timestamps
-- - TIMESTAMP WITH TIME ZONE (PostgreSQL, already 64-bit)

Conclusion

Here's the bottom line: timestamp storage isn't glamorous, but it's absolutely fundamental to building reliable systems.

The rules are simple (even if implementing them isn't always):

1. Always store UTC - This one rule eliminates most timezone headaches
2. Choose appropriate data types - TIMESTAMP WITH TIME ZONE, BIGINT, Date objects - pick the right tool
3. Index effectively - Your queries will thank you
4. Plan migrations - Don't try to switch everything at once
5. Test thoroughly - Especially around DST transitions and edge cases
6. Monitor performance - Watch those queries and optimize as needed

Follow these practices and you'll build systems with reliable timestamp handling, lightning-fast queries, and schemas that don't make your future self cry. Trust me, it's worth getting right the first time.

Further Reading

• Complete Guide to Unix Timestamps - Understand epoch time fundamentals
• Timezone Conversion Best Practices - Handle timezones correctly
• Handling Daylight Saving Time - Master DST transitions
• Common Timestamp Pitfalls - Avoid datetime bugs
• API Design: Timestamp Formats - Design APIs that work with your database
• Microservices Time Synchronization - Sync time across distributed databases

---

Need help with database timestamp design? Contact us for consultation.