How Databases Store Dates: MySQL vs PostgreSQL vs SQLite

Why Database Date Types Matter More Than You Think

The CSV batch converter was born out of a database migration. We were moving ~400,000 rows from MySQL to PostgreSQL, and the date column that worked perfectly in MySQL started producing timestamps that were off by five hours in Postgres. The root cause: MySQL's DATETIME type stores dates without timezone info, while Postgres's TIMESTAMP WITH TIME ZONE expects timezone-aware input and converts everything to UTC internally. The MySQL export was local-time dates masquerading as UTC.

This is one of the most common data engineering bugs, and it happens because each database handles dates differently in ways that are invisible until something breaks. This guide covers the three most popular open-source databases: MySQL, PostgreSQL, and SQLite.

MySQL

Date/Time Types

Type	Storage	Range	Precision	Timezone?
DATE	3 bytes	1000-01-01 to 9999-12-31	Day	No
TIME	3 bytes	-838:59:59 to 838:59:59	Second	No
DATETIME	5 bytes	1000-01-01 to 9999-12-31	Second (μs with fsp)	No
TIMESTAMP	4 bytes	1970-01-01 to 2038-01-19	Second (μs with fsp)	Yes (converted)
YEAR	1 byte	1901 to 2155	Year	No

The Critical Difference: DATETIME vs TIMESTAMP

This is where MySQL gets confusing. DATETIME and TIMESTAMP look similar in usage, but they handle timezones completely differently:

DATETIME stores the literal date and time you give it. If you insert '2024-06-29 14:30:00', it stores exactly that string of numbers. No timezone conversion happens. If your server's timezone changes, the stored value stays the same. This means DATETIME is timezone-naive — it has no concept of what timezone the value represents.

TIMESTAMP converts the input to UTC on storage and converts it back to the session's timezone on retrieval. If you insert '2024-06-29 14:30:00' while your session is in America/New_York (EDT, UTC-4), MySQL stores it as 2024-06-29 18:30:00 UTC. When you read it back, if your session timezone has changed (or a different client reads it with a different timezone), the displayed value changes.

-- DATETIME: stores as-is
SET time_zone = 'America/New_York';
INSERT INTO events (dt_col) VALUES ('2024-06-29 14:30:00');
SET time_zone = 'UTC';
SELECT dt_col FROM events;
-- → '2024-06-29 14:30:00' (unchanged — it doesn't know about timezones)

-- TIMESTAMP: converts to UTC on storage, back on retrieval
SET time_zone = 'America/New_York';
INSERT INTO events (ts_col) VALUES ('2024-06-29 14:30:00');
SET time_zone = 'UTC';
SELECT ts_col FROM events;
-- → '2024-06-29 18:30:00' (converted from EDT to UTC)

The trap: TIMESTAMP has a range limit of 2038-01-19 (the Y2038 problem — it uses a 32-bit Unix timestamp internally). DATETIME goes up to 9999-12-31. Many developers use DATETIME to avoid the range limit, but then lose timezone handling. There's no built-in MySQL type that gives you both a wide range and timezone awareness.

Fractional Seconds

MySQL supports fractional seconds precision (fsp) from 0 to 6 digits. You must declare it explicitly:

CREATE TABLE events (
  created_at DATETIME(3),   -- millisecond precision
  updated_at TIMESTAMP(6)   -- microsecond precision
);

-- Without fsp, fractional seconds are silently truncated
INSERT INTO events (created_at) VALUES ('2024-06-29 14:30:00.123456');
-- If created_at is DATETIME (no fsp), stored as '2024-06-29 14:30:00'
-- The .123456 is silently dropped. No warning.

PostgreSQL

Date/Time Types

Type	Storage	Range	Precision	Timezone?
DATE	4 bytes	4713 BC to 5874897 AD	Day	No
TIME	8 bytes	00:00:00 to 24:00:00	Microsecond	No
TIME WITH TIME ZONE	12 bytes	00:00:00 to 24:00:00	Microsecond	Yes
TIMESTAMP	8 bytes	4713 BC to 294276 AD	Microsecond	No
TIMESTAMP WITH TIME ZONE	8 bytes	4713 BC to 294276 AD	Microsecond	Yes (stored as UTC)
INTERVAL	16 bytes	±178000000 years	Microsecond	No

TIMESTAMP vs TIMESTAMP WITH TIME ZONE (TIMESTAMPTZ)

PostgreSQL's naming here is misleading. TIMESTAMP WITH TIME ZONE (commonly shortened to TIMESTAMPTZ) does not store the timezone. It converts the input to UTC and stores the UTC value. The timezone is used on input for conversion and on output for display, but the stored value is always UTC.

-- TIMESTAMPTZ: converts to UTC, displays in session timezone
SET timezone = 'America/New_York';
INSERT INTO events (ts_col) VALUES ('2024-06-29 14:30:00');
-- Stored as: 2024-06-29 18:30:00 UTC

SET timezone = 'Asia/Tokyo';
SELECT ts_col FROM events;
-- → '2024-06-30 03:30:00+09' (same instant, displayed in JST)

-- TIMESTAMP (without time zone): stores literally, no conversion
INSERT INTO events (naive_col) VALUES ('2024-06-29 14:30:00');
-- Stored as: 2024-06-29 14:30:00 (no timezone info)
SET timezone = 'Asia/Tokyo';
SELECT naive_col FROM events;
-- → '2024-06-29 14:30:00' (unchanged, no conversion)

Best practice: Almost always use TIMESTAMPTZ. The PostgreSQL documentation itself recommends this. The only good reason to use bare TIMESTAMP is when you're storing a "local time" concept that genuinely has no timezone (like a recurring daily event that happens at "9 AM local" regardless of where "local" is).

PostgreSQL's Input Flexibility

PostgreSQL accepts an unusually wide range of input formats:

SELECT 'June 29, 2024 2:30 PM EDT'::timestamptz;
SELECT '2024-06-29T14:30:00-04:00'::timestamptz;
SELECT '06/29/2024 14:30:00 EST'::timestamptz;
SELECT 'yesterday'::date;
SELECT 'now'::timestamptz;
SELECT 'epoch'::timestamptz;  -- → 1970-01-01 00:00:00+00

This flexibility is convenient but can mask format issues that would cause errors in other systems. Be explicit about formats when you know the data might move to another database.

SQLite

SQLite Has No Date Type

SQLite doesn't have dedicated date/time storage types. Dates are stored as one of three representations, and the programmer chooses which one to use:

Storage	Format	Example
TEXT	ISO 8601 string	'2024-06-29 14:30:00'
REAL	Julian day number	2460490.10417
INTEGER	Unix timestamp	1719671400

There's no type enforcement. You can store 'not a date' in a column intended for dates, and SQLite won't complain. The date/time functions (datetime(), strftime(), etc.) operate on any of the three formats:

-- Text dates
SELECT datetime('2024-06-29 14:30:00', '+1 hour');
-- → '2024-06-29 15:30:00'

-- Unix timestamps
SELECT datetime(1719671400, 'unixepoch');
-- → '2024-06-29 14:30:00'

-- Julian day numbers
SELECT julianday('2024-06-29 14:30:00');
-- → 2460490.10416667

-- Mixing formats works (SQLite figures it out)
SELECT strftime('%Y-%m-%d', 1719671400, 'unixepoch');
-- → '2024-06-29'

Timezone Handling

SQLite's date functions operate in UTC by default. There's no session timezone concept. You can convert to local time using the 'localtime' modifier, which uses the server's OS timezone:

-- UTC (default)
SELECT datetime('now');
-- → '2024-06-29 18:30:00'

-- Local time (OS timezone)
SELECT datetime('now', 'localtime');
-- → '2024-06-29 14:30:00' (if server is in EDT)

-- Specific timezone conversion isn't built in
-- You'd need to do it in application code

SQLite doesn't support IANA timezone names or arbitrary UTC offsets in its date functions. If you need timezone-aware storage, you'll need to handle conversions in your application layer — or store an explicit UTC offset alongside the date.

Migration Pitfalls

These are the bugs we see most often when people export CSVs from one database and import them into another:

MySQL DATETIME → PostgreSQL TIMESTAMPTZ. The MySQL export contains timezone-naive values like 2024-06-29 14:30:00. PostgreSQL interprets these using the session's timezone setting. If the Postgres session is in UTC but the MySQL data was entered in Eastern time, every timestamp shifts by 4–5 hours. Fix: either set the Postgres session timezone to match the MySQL data's implicit timezone before import, or convert the data explicitly.

PostgreSQL TIMESTAMPTZ → MySQL DATETIME. PostgreSQL exports UTC values. If you insert these into MySQL DATETIME columns as-is, the values are technically correct (they're UTC), but if the rest of your MySQL application assumes local time in that column, everything is offset. Fix: convert to the target timezone during export or import.

SQLite → anything. Since SQLite has no type enforcement, date columns might contain a mix of formats (ISO strings, Unix timestamps, plain text). There's no way to know without inspecting the data. Fix: sample the column and standardize the format before importing. This is exactly the scenario our CSV batch converter was built for.

Truncated fractional seconds. PostgreSQL stores microseconds by default. MySQL truncates to seconds unless you declared a fractional seconds precision. If you're moving data from Postgres to MySQL and the milliseconds matter, you'll lose them silently unless the MySQL column is DATETIME(3) or higher.

The Y2038 boundary. MySQL TIMESTAMP can't store dates after January 19, 2038. If you're migrating to MySQL TIMESTAMP from a system that has dates beyond 2038, those rows will fail. Use DATETIME instead if your data includes future dates.

Best Practices

Use timezone-aware types when available. PostgreSQL TIMESTAMPTZ, MySQL TIMESTAMP (with its range caveats). Store UTC, convert on display.

Always declare fractional seconds precision in MySQL if your data has sub-second precision. DATETIME(3) for milliseconds, DATETIME(6) for microseconds.

For SQLite, pick one format and stick with it. ISO 8601 text strings (YYYY-MM-DD HH:MM:SS) are the most readable and sort correctly. Unix timestamps are more compact and faster to compare. Don't mix them in the same column.

Document the timezone convention in your schema or data dictionary. If a DATETIME column in MySQL contains Eastern time values, there's nothing in the column metadata that says so. A code comment or documentation is the only way future developers (or future you) will know.

Test migrations with edge cases. Include DST transition timestamps, dates near the Y2038 boundary, dates with fractional seconds, and NULL values in your test set. These are the rows that break silently.

Quick Comparison

Feature	MySQL	PostgreSQL	SQLite
TZ-aware type	TIMESTAMP	TIMESTAMPTZ	None (manual)
TZ-naive type	DATETIME	TIMESTAMP	TEXT/INTEGER/REAL
Max precision	Microsecond (fsp 6)	Microsecond	Depends on format
Date range	1000–9999 (DATETIME)	4713 BC–294276 AD	Unlimited (TEXT)
Y2038 risk	Yes (TIMESTAMP type)	No	Yes (if using Unix INTEGER)
IANA timezone support	Needs tzdata loaded	Built-in	No
Type enforcement	Yes (strict mode)	Yes	No
Default precision	Seconds (no fsp)	Microseconds	N/A

Related Guides

For the Unix timestamp format that SQLite and MySQL TIMESTAMP use internally, see What Is a Unix Timestamp?. For the ISO 8601 format recommended for SQLite TEXT storage, see ISO 8601 Explained. If you're migrating data between databases and need to convert an entire column of dates, see How to Convert Dates in CSV Files. For handling DST transitions in database queries, see Daylight Saving Time: The Developer's Nightmare.