Database Timezone Best Practices: PostgreSQL, MySQL, MongoDB

Database Timezone Best Practices: PostgreSQL, MySQL, MongoDB

You are building a global application. Users are in New York, London, Tokyo, and Sydney. You need to store when orders are placed, when users log in, and when events happen.

You choose a database. You create a created_at column. You think: "I will just store the time."

Six months later, you are debugging timezone bugs. Orders show the wrong date. Reports are off by hours. Users are confused.

This is what happens when you don't think carefully about timezone handling in your database.

This guide covers best practices for storing and querying timezone-aware timestamps in PostgreSQL, MySQL, and MongoDB.

Use the free time zone converter here →

The Golden Rule (Same for All Databases)

Store UTC. Convert on display.

This rule applies to every database. It does not matter if you are using SQL or NoSQL. Store UTC timestamps in your database. Convert to local time only when displaying to users.

PostgreSQL: The Right Way

Data Types

PostgreSQL has two timestamp data types:

Important: PostgreSQL stores TIMESTAMPTZ values in UTC internally. The timezone is not stored — it is converted to UTC on insert and converted back on query.

Creating Tables

-- RIGHT: Use TIMESTAMPTZ
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW() AT TIME ZONE 'UTC',
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW() AT TIME ZONE 'UTC',
    delivered_at TIMESTAMPTZ
);

-- WRONG: Use TIMESTAMP without timezone
-- This stores local time with no timezone context
CREATE TABLE orders_wrong (
    id SERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT NOW()
);

Inserting Data

-- RIGHT: Store UTC
INSERT INTO orders (created_at) VALUES (NOW() AT TIME ZONE 'UTC');

-- RIGHT: Explicit UTC timestamp
INSERT INTO orders (created_at) VALUES ('2026-08-10T14:00:00Z');

-- RIGHT: From application with timezone
INSERT INTO orders (created_at) VALUES ('2026-08-10T10:00:00-04:00');
-- PostgreSQL converts this to UTC automatically

-- WRONG: Local time without timezone
INSERT INTO orders (created_at) VALUES ('2026-08-10 14:00:00');
-- What timezone? PostgreSQL doesn't know.

Querying Data

-- Store query returns UTC by default
SELECT created_at FROM orders;
-- Returns: 2026-08-10 14:00:00+00

-- Convert to specific timezone on display
SELECT created_at AT TIME ZONE 'America/New_York' AS created_at_nyc
FROM orders;
-- Returns: 2026-08-10 10:00:00

-- Convert to user's timezone (store user_timezone in users table)
SELECT created_at AT TIME ZONE u.timezone AS created_at_local
FROM orders o
JOIN users u ON o.user_id = u.id;

Indexing

-- Index on timestamptz columns
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Range queries work efficiently
SELECT * FROM orders
WHERE created_at BETWEEN '2026-08-01T00:00:00Z' AND '2026-08-31T23:59:59Z';

Common Mistakes in PostgreSQL

MySQL: The Right Way

Data Types

MySQL has two timestamp-related data types:

Recommendation: Use DATETIME with UTC values for all new applications.

Creating Tables

-- RIGHT: Use DATETIME with UTC default
CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    created_at DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP()),
    updated_at DATETIME NOT NULL DEFAULT (UTC_TIMESTAMP()) ON UPDATE (UTC_TIMESTAMP()),
    delivered_at DATETIME
);

-- Also RIGHT: Use TIMESTAMP (but beware of 2038)
CREATE TABLE orders_with_timestamp (
    id INT PRIMARY KEY AUTO_INCREMENT,
    created_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP(),
    updated_at TIMESTAMP NOT NULL DEFAULT UTC_TIMESTAMP() ON UPDATE UTC_TIMESTAMP()
);

Inserting Data

-- RIGHT: Store UTC
INSERT INTO orders (created_at) VALUES (UTC_TIMESTAMP());

-- RIGHT: Explicit UTC timestamp
INSERT INTO orders (created_at) VALUES ('2026-08-10 14:00:00');
-- But you must ensure your session timezone is UTC

-- RIGHT: Set session timezone to UTC
SET time_zone = '+00:00';
INSERT INTO orders (created_at) VALUES (NOW());

-- WRONG: Local time without timezone
INSERT INTO orders (created_at) VALUES (NOW());
-- If session timezone is not UTC, this is wrong

Session Timezone Configuration

-- Check current timezone
SELECT @@session.time_zone;

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

-- Or set globally (requires SUPER privilege)
SET GLOBAL time_zone = '+00:00';

Best practice: Set the MySQL server to use UTC by default.

# my.cnf or my.ini
default-time-zone = '+00:00'

Querying Data

-- Store query returns UTC
SELECT created_at FROM orders;
-- Returns: 2026-08-10 14:00:00

-- Set session timezone for display
SET time_zone = 'America/New_York';
SELECT created_at FROM orders;
-- Returns: 2026-08-10 10:00:00

-- Convert to specific timezone using CONVERT_TZ
SELECT CONVERT_TZ(created_at, '+00:00', 'America/New_York') AS created_at_nyc
FROM orders;

Indexing

-- Index on datetime columns
CREATE INDEX idx_orders_created_at ON orders (created_at);

-- Range queries with UTC values
SELECT * FROM orders
WHERE created_at BETWEEN '2026-08-01 00:00:00' AND '2026-08-31 23:59:59';

Common Mistakes in MySQL

MongoDB: The Right Way

Data Type

MongoDB uses the Date() type for timestamps.

Key fact: MongoDB stores all Date() values in UTC. You cannot store local time in MongoDB. This is actually a good thing — it forces you to do the right thing.

Creating Documents

// RIGHT: Store Date() with new Date()
db.orders.insertOne({
    order_id: 'ORD-001',
    created_at: new Date(), // Automatically UTC
    updated_at: new Date(),
    delivered_at: null
});

// RIGHT: Store ISO string (converted to Date)
db.orders.insertOne({
    created_at: ISODate('2026-08-10T14:00:00Z')
});

// RIGHT: Store explicit UTC
db.orders.insertOne({
    created_at: new Date('2026-08-10T14:00:00Z')
});

// WRONG: Storing as string
// This loses type information and makes queries harder
db.orders.insertOne({
    created_at: '2026-08-10 14:00:00' // Wrong!
});

Querying Data

// Store query returns UTC
const order = db.orders.findOne({ order_id: 'ORD-001' });
console.log(order.created_at); // ISODate("2026-08-10T14:00:00Z")

// Convert to ISO string (UTC)
console.log(order.created_at.toISOString()); // 2026-08-10T14:00:00.000Z

// Query by date range (always use UTC)
const start = new Date('2026-08-01T00:00:00Z');
const end = new Date('2026-08-31T23:59:59Z');
const orders = db.orders.find({
    created_at: { $gte: start, $lte: end }
}).toArray();

Aggregation with Timezones

// Group orders by day (in UTC)
db.orders.aggregate([
    {
        $group: {
            _id: {
                year: { $year: '$created_at' },
                month: { $month: '$created_at' },
                day: { $dayOfMonth: '$created_at' }
            },
            count: { $sum: 1 }
        }
    }
]);

// Group orders by day in user's timezone
// Need to use $dateToString with timezone
db.orders.aggregate([
    {
        $group: {
            _id: {
                $dateToString: {
                    format: '%Y-%m-%d',
                    date: '$created_at',
                    timezone: 'America/New_York'
                }
            },
            count: { $sum: 1 }
        }
    }
]);

Indexing

// Index on date field
db.orders.createIndex({ created_at: 1 });

// Compound index with date
db.orders.createIndex({ user_id: 1, created_at: -1 });

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

Common Mistakes in MongoDB

Migration Guide: Converting Local Time to UTC

PostgreSQL Migration

-- Step 1: Add a new UTC column
ALTER TABLE orders ADD COLUMN created_at_utc TIMESTAMPTZ;

-- Step 2: Convert existing data
UPDATE orders
SET created_at_utc = created_at AT TIME ZONE 'America/New_York'
WHERE created_at_utc IS NULL;

-- Step 3: Drop old column
ALTER TABLE orders DROP COLUMN created_at;

-- Step 4: Rename new column
ALTER TABLE orders RENAME COLUMN created_at_utc TO created_at;

-- Step 5: Add default for new rows
ALTER TABLE orders ALTER COLUMN created_at SET DEFAULT NOW() AT TIME ZONE 'UTC';

MySQL Migration

-- Step 1: Add a new UTC column
ALTER TABLE orders ADD COLUMN created_at_utc DATETIME;

-- Step 2: Set session to UTC
SET time_zone = '+00:00';

-- Step 3: Convert existing data
UPDATE orders
SET created_at_utc = CONVERT_TZ(created_at, 'America/New_York', '+00:00');

-- Step 4: Drop old column
ALTER TABLE orders DROP COLUMN created_at;

-- Step 5: Rename new column
ALTER TABLE orders RENAME COLUMN created_at_utc TO created_at;

-- Step 6: Add default for new rows
ALTER TABLE orders MODIFY created_at DATETIME DEFAULT UTC_TIMESTAMP();

MongoDB Migration

// Step 1: Find all documents with string dates
const docs = db.orders.find({ created_at: { $type: 'string' } });

// Step 2: Convert each one
docs.forEach(doc => {
    const date = new Date(doc.created_at);
    db.orders.updateOne(
        { _id: doc._id },
        { $set: { created_at: date } }
    );
});

// Step 3: Verify all are Date type
const stillString = db.orders.find({ created_at: { $type: 'string' } }).count();
if (stillString > 0) {
    // Handle remaining string dates
}

Timezone Query Patterns

Daily Aggregations

PostgreSQL:

-- Group by day in UTC
SELECT DATE(created_at) AS day, COUNT(*) AS count
FROM orders
GROUP BY DATE(created_at);

-- Group by day in user's timezone
SELECT DATE(created_at AT TIME ZONE 'America/New_York') AS day, COUNT(*) AS count
FROM orders
GROUP BY DATE(created_at AT TIME ZONE 'America/New_York');

MySQL:

-- Group by day in UTC (session timezone must be UTC)
SELECT DATE(created_at) AS day, COUNT(*) AS count
FROM orders
GROUP BY DATE(created_at);

-- Group by day in user's timezone
SET time_zone = 'America/New_York';
SELECT DATE(created_at) AS day, COUNT(*) AS count
FROM orders
GROUP BY DATE(created_at);

MongoDB:

// Group by day in UTC
db.orders.aggregate([
    {
        $group: {
            _id: {
                $dateTrunc: { date: '$created_at', unit: 'day' }
            },
            count: { $sum: 1 }
        }
    }
]);

// Group by day in user's timezone
db.orders.aggregate([
    {
        $group: {
            _id: {
                $dateToString: {
                    format: '%Y-%m-%d',
                    date: '$created_at',
                    timezone: 'America/New_York'
                }
            },
            count: { $sum: 1 }
        }
    }
]);

Quick Reference Card

PostgreSQL

-- Data type: TIMESTAMPTZ
-- Store: NOW() AT TIME ZONE 'UTC'
-- Query: created_at AT TIME ZONE 'America/New_York'
-- Default: NOW() AT TIME ZONE 'UTC'

MySQL

-- Data type: DATETIME (preferred) or TIMESTAMP
-- Store: UTC_TIMESTAMP()
-- Query: CONVERT_TZ(created_at, '+00:00', 'America/New_York')
-- Default: UTC_TIMESTAMP()
-- Server: default-time-zone = '+00:00'

MongoDB

// Data type: Date()
// Store: new Date()
-- Query: Use UTC dates
-- Default: new Date() (automatic UTC)

Frequently Asked Questions

Should I use TIMESTAMP or DATETIME in MySQL?

Use DATETIME. It does not have the 2038 problem and stores dates beyond 2038.

Does PostgreSQL store timezone in TIMESTAMPTZ?

No. PostgreSQL converts to UTC on insert and never stores the original timezone. Timezone is only for display.

Does MongoDB store timezone?

No. MongoDB stores Date() as UTC milliseconds. No timezone is stored.

How do I query by day in a user's timezone?

Use AT TIME ZONE in PostgreSQL, CONVERT_TZ in MySQL, or $dateToString with timezone in MongoDB.

What about storing user's timezone?

Store user's timezone in the users table as an IANA identifier (e.g., "America/New_York").

Final Thoughts

Three things to remember:

1. Store UTC in every database — No exceptions
2. Use the right data type — TIMESTAMPTZ (PostgreSQL), DATETIME (MySQL), Date() (MongoDB)
3. Convert on display — Never show UTC to users

Check timezone conversions instantly → /timezone-converter

Tags: database timezone best practices, PostgreSQL timezone, MySQL timezone, MongoDB timezone, timestamptz PostgreSQL, UTC storage database, database timestamp best practices, SQL timezone handling, NoSQL timezone storage, timezone data types database, PostgreSQL timestamptz vs timestamp, MySQL UTC_TIMESTAMP, MongoDB Date timezone