PostgreSQL vs MySQL Timestamp Storage: What No One Tells You

Quick Answer: PostgreSQL's TIMESTAMPTZ stores all times as UTC internally and converts to session timezone on retrieval—always correct across timezones. MySQL's DATETIME is timezone-naive (stores exactly what you insert), while MySQL's TIMESTAMP converts to UTC but has a Y2038 limit. ORMs like Prisma and Sequelize silently coerce timestamps to UTC regardless of your intention, breaking timezone-aware code.

Your production database just served a timestamp from 3 PM yesterday to a user in Tokyo, and they saw it as 6 AM today. You check the code—no explicit timezone conversion. You check the database—the value looks correct. The bug lives in the ORM layer, silently converting your timestamps in ways the documentation never warns you about. This is the silent killer of distributed systems, and most teams don't discover it until they're operating across continents.

PostgreSQL TIMESTAMPTZ vs MySQL DATETIME: The Fundamental Difference

The core issue is philosophical: PostgreSQL chose to always store the correct moment in time, while MySQL chose to store what the user typed. This distinction cascades through your entire application.

-- PostgreSQL: Always stores UTC internally, displays in session timezone
CREATE TABLE events (id SERIAL PRIMARY KEY, created_at TIMESTAMPTZ);

-- Insert time in New York timezone (-5 hours from UTC)
INSERT INTO events (created_at) VALUES ('2026-05-07 14:30:00-05');

-- Session 1: New York timezone sees local time
SET timezone = 'America/New_York';
SELECT created_at FROM events;
-- → 2026-05-07 14:30:00-05 (same moment, local display)

-- Session 2: Tokyo timezone sees the same moment in Tokyo time
SET timezone = 'Asia/Tokyo';
SELECT created_at FROM events;
-- → 2026-05-08 03:30:00+09 (same UTC moment: 2026-05-07 19:30:00 UTC)

-- MySQL DATETIME: Stores exactly what you insert, no conversion
CREATE TABLE events (id INT AUTO_INCREMENT PRIMARY KEY, created_at DATETIME);

-- Insert the same timestamp
INSERT INTO events (created_at) VALUES ('2026-05-07 14:30:00');
-- Stored literally: 2026-05-07 14:30:00 (no timezone info attached)

-- No matter what timezone the session is in:
SET time_zone = '+09:00';
SELECT created_at FROM events;
-- → 2026-05-07 14:30:00 (exactly as stored, NOT converted to 03:30)

-- MySQL TIMESTAMP is different—it converts like PostgreSQL
CREATE TABLE events_ts (created_at TIMESTAMP);
INSERT INTO events_ts (created_at) VALUES ('2026-05-07 14:30:00');
-- Assumes session timezone for input, stores UTC internally
SET GLOBAL time_zone = '+00:00';
SET SESSION time_zone = '+00:00';
-- This is the safe MySQL approach for timezone-aware apps

Why ORM Coercion Silently Breaks Your Timestamps

ORMs abstract the database layer, but they also abstract away critical timezone logic. Prisma, Sequelize, and TypeORM all make assumptions about how timestamps should behave—assumptions that don't match your database's actual behavior.

// Prisma schema (common mistake)
model User {
  id        String   @id @default(cuid())
  createdAt DateTime  // Maps to PostgreSQL TIMESTAMP (no timezone!)
}

// Input from API
const user = await prisma.user.create({
  data: {
    createdAt: new Date('2026-05-07T14:30:00+02:00')
  }
});

// What Prisma does internally:
// 1. Takes your ISO string with +02:00 offset
// 2. Converts to UTC: 2026-05-07T12:30:00Z
// 3. Stores in TIMESTAMP column (which discards timezone info)
// 4. Result: 2026-05-07 12:30:00 (your original 14:30 is lost!)

// Correct Prisma schema
model User {
  id        String   @id @default(cuid())
  createdAt DateTime @db.Timestamptz  // Explicitly use TIMESTAMPTZ!
}
// Now Prisma stores: 2026-05-07 12:30:00+00 (UTC, correct moment preserved)

// Sequelize with MySQL DATETIME (timezone-naive)
const User = sequelize.define('User', {
  createdAt: {
    type: DataTypes.DATE,  // Maps to DATETIME in MySQL
    allowNull: false
  }
});

// Insert with timezone offset
await User.create({
  createdAt: new Date('2026-05-07T14:30:00+02:00')
});

// Sequelize converts input to UTC by default, then stores:
// MySQL stores: 2026-05-07 12:30:00 (the UTC moment, not original 14:30)
// But DATETIME doesn't preserve timezone context!
// When you retrieve it, you get 2026-05-07 12:30:00 with no timezone marker
// Your app now thinks this is a UTC moment, but it was meant to be +02:00

// Safe Sequelize approach (PostgreSQL):
const User = sequelize.define('User', {
  createdAt: {
    type: DataTypes.DATE,  // Will use TIMESTAMPTZ in PostgreSQL
    allowNull: false,
    dialectOptions: {
      timezone: 'UTC'  // Explicit UTC enforcement
    }
  }
});

The MySQL TIMESTAMP Y2038 Problem—Still Unresolved in 2026

MySQL's TIMESTAMP type uses a signed 32-bit Unix timestamp, capping out at January 19, 2038 at 03:14:07 UTC. Your code written today will fail in 12 years. Major cloud providers are already warning about this.

-- MySQL 8.0 (and all current versions through 2026)
CREATE TABLE events (ts TIMESTAMP);

-- This works fine
INSERT INTO events VALUES ('2026-05-07 12:30:00');

-- This fails with error 1292
INSERT INTO events VALUES ('2050-01-01 00:00:00');
-- ERROR 1292 (22007): Incorrect datetime value: '2050-01-01 00:00:00'

-- MySQL functions were updated in 8.0.28 to support 64-bit timestamps
SELECT UNIX_TIMESTAMP('2100-01-01 00:00:00');
-- → 4102272000 (works, but ONLY in functions, not columns)

-- MariaDB 11.5.1+ solved this with unsigned 32-bit interpretation
-- Supporting dates up to 2106-02-07
-- But if you're using MySQL 8.0: migrate away from TIMESTAMP

-- Migration strategy: DATETIME (8 bytes, no Y2038 limit)
ALTER TABLE events MODIFY COLUMN ts DATETIME;
-- Range: 1000-01-01 to 9999-12-31 ✓

-- Or store as BIGINT UNSIGNED (application-level Unix timestamp)
ALTER TABLE events ADD COLUMN ts_unix BIGINT UNSIGNED;
UPDATE events SET ts_unix = UNIX_TIMESTAMP(ts);

-- Helper view for backward compatibility
CREATE VIEW events_readable AS
SELECT id, FROM_UNIXTIME(ts_unix) as ts FROM events;

-- In application code (Node.js example)
const events = await db.query('SELECT ts_unix FROM events');
events.forEach(e => {
  const jsDate = new Date(e.ts_unix * 1000);  // Convert seconds to milliseconds
  console.log(jsDate.toISOString());
});

Storage Format Comparison: Bytes, Range, and Timezone Awareness

Type	Storage	Range	Timezone Handling	Y2038 Safe
PostgreSQL TIMESTAMPTZ	8 bytes	4713 BC to 294276 AD	Stores UTC, displays session TZ	✓ Yes
PostgreSQL TIMESTAMP	8 bytes	4713 BC to 294276 AD	None (naive)	✓ Yes
MySQL TIMESTAMP	4 bytes	1970 to 2038	UTC store, session display	✗ No
MySQL DATETIME	8 bytes	1000 to 9999	None (naive)	✓ Yes
BIGINT UNSIGNED (Unix ts)	8 bytes	0 to 2106	None (app-layer)	✓ Yes

Common Mistakes and How to Fix Them

Mistake: Assuming MySQL DATETIME respects your server timezone

-- ✗ WRONG: Belief that DATETIME converts with server timezone
SET GLOBAL time_zone = 'America/New_York';
INSERT INTO users (created_at) VALUES ('2026-05-07 14:30:00');
SELECT created_at FROM users;  -- Returns: 2026-05-07 14:30:00
SET GLOBAL time_zone = 'Asia/Tokyo';
SELECT created_at FROM users;  -- STILL returns: 2026-05-07 14:30:00 (not converted!)

-- ✓ RIGHT: Use TIMESTAMP if you need timezone conversion
SET GLOBAL time_zone = 'America/New_York';
INSERT INTO users (created_at) VALUES ('2026-05-07 14:30:00');  -- Assumes America/New_York
SELECT created_at FROM users;  -- 2026-05-07 14:30:00-05 (internal UTC: 2026-05-07 19:30:00)
SET GLOBAL time_zone = 'Asia/Tokyo';
SELECT created_at FROM users;  -- 2026-05-08 04:30:00+09 (same UTC moment, converted)

MySQL DATETIME is intentionally naive—it stores your input without conversion. Many developers assume server timezone settings apply to DATETIME columns, leading to data corruption when migrating between timezone regions. Use TIMESTAMP only if you need automatic timezone conversion, or handle all timezone logic in your application layer with DATETIME.

Mistake: Mixing TIMESTAMP and DATETIME without understanding the difference

-- ✗ WRONG: Mixing types in same schema
CREATE TABLE orders (
  id INT PRIMARY KEY,
  placed_at TIMESTAMP,           -- Converts to UTC
  shipped_at DATETIME,           -- Stored as-is (naive)
  delivered_at TIMESTAMP         -- Converts to UTC
);

-- Confusing behavior in queries:
WHERE placed_at > '2026-01-01'    -- Interprets as current session TZ, converts to UTC
AND shipped_at > '2026-01-01'     -- Interprets literally (no conversion)

-- ✓ RIGHT: Standardize on one approach per table
CREATE TABLE orders (
  id INT PRIMARY KEY,
  placed_at DATETIME,            -- All naive storage
  shipped_at DATETIME,
  delivered_at DATETIME,
  placed_at_tz VARCHAR(50)       -- Store timezone separately if needed
);

-- Or use PostgreSQL and always use TIMESTAMPTZ:
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  placed_at TIMESTAMPTZ,         -- All timezone-aware
  shipped_at TIMESTAMPTZ,
  delivered_at TIMESTAMPTZ
);

Mixing storage types within a schema creates maintenance nightmares. Different developers interpret TIMESTAMP vs DATETIME differently based on their prior experience. Standardize at the schema level: either all naive (DATETIME in MySQL) or all timezone-aware (TIMESTAMPTZ in PostgreSQL).

Mistake: Forgetting to specify @db.Timestamptz in Prisma with PostgreSQL


// ✗ WRONG: Prisma defaults to TIMESTAMP (no timezone)
model User {
  id        String   @id
  createdAt DateTime @default(now())  // Maps to TIMESTAMP, not TIMESTAMPTZ!
}

// User created at 2026-05-07T14:30:00+02:00
// Prisma converts to UTC: 2026-05-07T12:30:00Z
// Stores in TIMESTAMP: 2026-05-07 12:30:00 (timezone info lost!)
// When retrieved: 2026-05-07 12:30:00 (no way to know original offset was +02:00)

// ✓ RIGHT: Explicitly use @db.Timestamptz
model User {
  id        String   @id
  createdAt DateTime @default(now()) @db.Timestamptz  // Explicit!
  updatedAt DateTime @updatedAt      @db.Timestamptz
}

// Same input: 2026-05-07T14:30:00+02:00
// Prisma stores in TIMESTAMPTZ: 2026-05-07 12:30:00+00 (UTC, correct moment preserved)
// When retrieved with different session TZ: still shows correct UTC moment

Prisma's documentation emphasizes that DateTime maps to TIMESTAMP by default in PostgreSQL. This is a footgun because TIMESTAMP lacks timezone awareness. The fix is mechanical—add @db.Timestamptz—but the cost of missing it is data corruption across timezones. Always explicitly annotate timestamp columns in Prisma schemas.

Mistake: Storing application timestamps in local time instead of UTC


// ✗ WRONG: Storing local system time
const now = new Date();  // System time (could be any timezone)
const user = await User.create({
  createdAt: now  // If server is in New York, this is EDT
});

// Database stores this as if it were UTC
// When you query from Tokyo server: timestamp appears 5 hours in the future!

// ✓ RIGHT: Always store UTC, convert for display only
const now = new Date().toISOString();  // Always UTC: 2026-05-07T19:30:00.000Z
const user = await User.create({
  createdAt: now
});

// On retrieval, convert to user timezone
function formatForUser(utcDate, userTimezone) {
  const formatter = new Intl.DateTimeFormat('en-US', {
    timeZone: userTimezone,
    year: 'numeric',
    month: '2-digit',
    day: '2-digit',
    hour: '2-digit',
    minute: '2-digit'
  });
  return formatter.format(new Date(utcDate));
}

// Usage
const userTz = 'America/New_York';
const displayTime = formatForUser(user.createdAt, userTz);  // Local time for display

Local time storage is the original sin of timestamp handling. It works fine on single-server deployments but breaks immediately with geographic distribution. The pattern here is universal: store UTC always, convert to display timezone only when rendering to users.

Frequently Asked Questions

Should I use TIMESTAMP or DATETIME in MySQL?

Use DATETIME for new projects. MySQL's TIMESTAMP has a hard Y2038 limit (January 19, 2038) due to its 4-byte signed integer storage. DATETIME uses 8 bytes and supports dates up to year 9999. The only reason to use TIMESTAMP is if you need automatic timezone conversion and you're sure all your data will be before 2038—which is increasingly rare. If you need timezone awareness, handle it at the application layer or migrate to PostgreSQL.

What is the difference between TIMESTAMPTZ and TIMESTAMP in PostgreSQL?

TIMESTAMPTZ (timestamp with time zone) stores the absolute moment in time as UTC internally and displays in the session timezone. TIMESTAMP (without time zone) stores a naive datetime value with no timezone info. For distributed systems and APIs, TIMESTAMPTZ is always correct. Use TIMESTAMP only when storing local times (like "office opens at 9 AM") where timezone isn't meaningful. See the timestamp converter tool to test conversions live.

Does MySQL TIMESTAMP support timezone?

Yes, but with caveats. MySQL TIMESTAMP stores a moment in UTC internally and converts display based on the session timezone setting. However, this is not the same as storing timezone information. The actual offset/timezone name is lost. Additionally, TIMESTAMP is limited to 1970-2038 due to 32-bit storage. For timezone-aware applications, PostgreSQL TIMESTAMPTZ is superior. If you must use MySQL, DATETIME with application-layer timezone handling is more reliable long-term.

How does Prisma handle timestamp timezones?

Prisma's DateTime type maps to TIMESTAMP (no timezone) in PostgreSQL and DATETIME in MySQL by default. Prisma always converts JavaScript Date objects to UTC before storage. This silently loses the original timezone offset. The fix is explicit: use @db.Timestamptz in Prisma schemas for PostgreSQL to map to the timezone-aware column type. This ensures Prisma stores the complete UTC moment rather than discarding timezone info. See examples with the timestamp API to test ORM behavior programmatically.

Production-Ready Schema Examples


// PostgreSQL with Prisma (correct approach)
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Event {
  id        String   @id @default(cuid())
  name      String
  // ✓ Always use @db.Timestamptz for timezone-aware times
  createdAt DateTime @default(now()) @db.Timestamptz
  updatedAt DateTime @updatedAt      @db.Timestamptz
  // ✓ Times with business meaning (office hours) can use TIMESTAMP
  scheduledAt DateTime              @db.Timestamp
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  createdAt DateTime @default(now()) @db.Timestamptz
  lastLogin DateTime?                @db.Timestamptz
}


// Sequelize with MySQL (safe approach for timezone-aware data)
const sequelize = new Sequelize(database, user, password, {
  host: 'localhost',
  dialect: 'mysql',
  timezone: '+00:00',  // Force UTC globally
  logging: false
});

const User = sequelize.define('User', {
  id: {
    type: DataTypes.UUID,
    defaultValue: DataTypes.UUIDV4,
    primaryKey: true
  },
  email: DataTypes.STRING,
  createdAt: {
    type: DataTypes.DATE,
    defaultValue: DataTypes.NOW,
    allowNull: false
    // No dialect options needed if global timezone is UTC
  },
  lastLogin: {
    type: DataTypes.DATE,
    allowNull: true
  }
}, {
  createdAt: 'createdAt',
  updatedAt: 'updatedAt'
});

// When retrieving, timestamps are UTC Date objects
const user = await User.findByPk(userId);
// user.createdAt is a JavaScript Date in UTC

// Convert to user's local timezone for display
const userTz = user.timezone || 'UTC';  // Store user's timezone preference
const localTime = user.createdAt.toLocaleString('en-US', {
  timeZone: userTz
});


-- MySQL schema (Y2038 safe, timezone handling in app layer)
CREATE TABLE events (
  id CHAR(36) PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  -- Using DATETIME instead of TIMESTAMP (no Y2038 limit)
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_created (created_at)
);

CREATE TABLE users (
  id CHAR(36) PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  timezone VARCHAR(50) DEFAULT 'UTC',  -- Store user's preferred timezone
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  last_login DATETIME,
  INDEX idx_created (created_at)
);

-- Safe global setting for all sessions
SET GLOBAL time_zone = '+00:00';

TypeORM Configuration (Handling Across Multiple Databases)


import { Entity, PrimaryGeneratedColumn, Column, CreateDateColumn, UpdateDateColumn } from 'typeorm';

@Entity()
export class Event {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column()
  name: string;

  // TypeORM with PostgreSQL: uses TIMESTAMPTZ by default for Date columns
  // TypeORM with MySQL: uses DATETIME (timezone-naive)
  @CreateDateColumn()  // Created_at: DateTime
  createdAt: Date;

  @UpdateDateColumn()  // Updated_at: DateTime
  updatedAt: Date;
}

// Explicit column types for maximum clarity across databases
@Entity()
export class ExplicitEvent {
  @PrimaryGeneratedColumn('uuid')
  id: string;

  @Column('timestamp with time zone')  // PostgreSQL TIMESTAMPTZ
  createdAt: Date;

  @Column('datetime')  // MySQL DATETIME, platform-specific
  submittedAt: Date;
}

// Safe approach: handle timezones at service layer
@Injectable()
export class EventService {
  constructor(private repo: Repository) {}

  async create(name: string, creatorTz: string): Promise {
    // Always create dates in UTC
    const now = new Date();  // JavaScript Date is always UTC-based
    const event = this.repo.create({ name, createdAt: now });
    return this.repo.save(event);
  }

  async getUserTime(eventId: string, userTz: string): Promise {
    const event = await this.repo.findOneBy({ id: eventId });
    // Convert UTC to user timezone for display
    return event.createdAt.toLocaleString('en-US', { timeZone: userTz });
  }
}

Debugging Timestamp Issues: Tools and Techniques


# Verify your database server timezone settings
# PostgreSQL
psql -c "SHOW timezone;"
# → Should return 'UTC' or similar

# MySQL
mysql -e "SELECT @@GLOBAL.time_zone, @@SESSION.time_zone;"
# → Should both be '+00:00'

# Test timestamp conversion with the Unix Timestamp API
# See exact values being stored across timezones
curl "https://unixcalculator.com/api/timestamp?value=2026-05-07T14:30:00Z"


-- PostgreSQL: Check what's actually stored vs displayed
CREATE TABLE tz_test (
  id SERIAL PRIMARY KEY,
  ts_tz TIMESTAMPTZ,
  ts_naive TIMESTAMP
);

INSERT INTO tz_test VALUES 
  (DEFAULT, '2026-05-07 14:30:00+02:00', '2026-05-07 14:30:00');

-- Show raw bytes (actual storage)
SELECT ts_tz::text as "TIMESTAMPTZ text", 
       ts_naive::text as "TIMESTAMP text",
       EXTRACT(EPOCH FROM ts_tz) as "TIMESTAMPTZ epoch",
       EXTRACT(EPOCH FROM ts_naive) as "TIMESTAMP epoch"
FROM tz_test;

-- Results show TIMESTAMPTZ correctly stores the moment (2026-05-07 12:30:00 UTC)
-- while TIMESTAMP stores naive local value (2026-05-07 14:30:00 with no TZ)

-- Now change session timezone
SET timezone = 'Asia/Tokyo';
SELECT ts_tz, ts_naive FROM tz_test;
-- TIMESTAMPTZ adjusts display to +09 (2026-05-08 03:30:00+09)
-- TIMESTAMP stays the same (2026-05-07 14:30:00)


// Node.js debugging: Inspect what ORMs send to database
// For Prisma with PostgreSQL adapter, enable query logging
const prisma = new PrismaClient({
  log: [
    { emit: 'event', level: 'query' },
  ],
});

prisma.$on('query', (e) => {
  console.log('Query: ' + e.query);
  console.log('Duration: ' + e.duration + 'ms');
  // Look for timezone information in the query
  // Example: "INSERT INTO \"User\" (\"createdAt\") VALUES ($1)"
  // Check what $1 actually contains by examining the prepared statement
});

// Direct database inspection
const result = await prisma.user.findUnique({
  where: { id: 'test-id' },
  select: { createdAt: true }
});

console.log(result.createdAt);  // JavaScript Date object (always represents UTC internally)
console.log(result.createdAt.toISOString());  // Shows the UTC moment

Key Takeaways

PostgreSQL TIMESTAMPTZ always stores the correct moment in UTC. Use it for any timestamp that must be comparable across timezones. MySQL offers no equivalent—TIMESTAMP has Y2038 limits and no timezone preservation, while DATETIME is naive.
MySQL DATETIME is timezone-naive by design. It stores exactly what you insert with no conversion. If you need timezone conversion, use TIMESTAMP with global time_zone = '+00:00', or handle all conversion in your application layer. Never assume server timezone settings apply to DATETIME columns.
ORMs silently coerce all timestamps to UTC (Prisma, Sequelize, TypeORM) before storage. This is correct behavior, but you must ensure your schema matches: use @db.Timestamptz in Prisma schemas for PostgreSQL, and globally set MySQL timezone to UTC. Mixing approaches causes data corruption.
MySQL TIMESTAMP has a hard Y2038 limit. New projects must use DATETIME (8 bytes, year 1000-9999) or migrate to PostgreSQL. MariaDB 11.5.1+ fixed this, but MySQL 8.0 remains broken. Check your version and plan migrations accordingly.
The safest pattern: store everything as UTC in the database, convert to user timezone only for display. This works across all databases and ORM versions. Use the Python timezone cheatsheet for reference implementations in application code.

Real-World Consequences: A Case Study


// Actual bug found in production: Prisma with PostgreSQL TIMESTAMP (no TZ)
// Startup in Berlin (UTC+02:00), using TIMESTAMP column

// User in New York creates event at 2026-05-07 14:30:00-05
// Input timestamp: 2026-05-07T19:30:00Z (UTC equivalent)

// Prisma converts to UTC: ✓ correct (2026-05-07 19:30:00)
// Stores in TIMESTAMP: ✗ stores just the time without UTC marker: 2026-05-07 19:30:00

// Six months later: Timezone offset changes (daylight saving end)
// Berlin shifts from UTC+02:00 to UTC+01:00
// Query in Berlin session: "SELECT created_at FROM events WHERE id = 123"
// Returns: 2026-05-07 19:30:00 (no timezone info, treated as local time)

// New York user checks same event:
// Their timezone: UTC-05:00 (may differ from original)
// Without timezone info, app treats 2026-05-07 19:30:00 as local time
// Displays as 2026-05-08 00:30:00 EDT or 2026-05-08 01:30:00 EST (wrong!)

// Fix: Use TIMESTAMPTZ
ALTER TABLE events ALTER COLUMN created_at TYPE TIMESTAMPTZ;
// Now same timestamp always represents same moment across all timezones