How Databases Store Time

Learning Objectives

Understand the binary and text representations of DATE, TIME, TIMESTAMP, and TIMESTAMPTZ in PostgreSQL
Identify the dangers of storing dates and times as plain strings or integers
Recognize implicit type conversion traps that cause silent data corruption

PostgreSQL Temporal Types at a Glance

PostgreSQL provides four main types for representing time:

Type	Storage	Range	Precision
`DATE`	4 bytes	4713 BC – 294276 AD	1 day
`TIME`	8 bytes	00:00:00 – 24:00:00	1 microsecond
`TIMESTAMP`	8 bytes	4713 BC – 294276 AD	1 microsecond
`TIMESTAMPTZ`	8 bytes	4713 BC – 294276 AD	1 microsecond

Internally, PostgreSQL stores TIMESTAMP as the number of microseconds since January 1, 2000, 00:00:00. It is a pure binary integer — there is no timezone information embedded in the value itself. TIMESTAMPTZ stores the same integer but PostgreSQL normalizes the input to UTC before storing and converts it to the session timezone on retrieval. This means TIMESTAMPTZ and TIMESTAMP take identical space, but their behavior differs fundamentally.

When you query SELECT now(), PostgreSQL returns a TIMESTAMPTZ. When you cast it to TIMESTAMP, you silently strip the timezone information.

Text Serialization and Precision

When a TIMESTAMP column is serialized to text (e.g., via JDBC, JSON, or psql output), PostgreSQL formats it as YYYY-MM-DD HH:MI:SS.ffffff. The microsecond fraction is included only if non-zero. This creates a subtle testing trap: two timestamps that differ only in microseconds look identical in a log but are not equal in the database.

SELECT '2024-03-15 14:30:00'::timestamp = '2024-03-15 14:30:00.000001'::timestamp;
-- Result: false

A QA tester must verify what precision the application uses when reading or writing timestamps. If the application truncates to seconds, repeating an operation within the same second can return a stale record.

Why Storing Dates as Strings is Dangerous

A column defined as VARCHAR(20) can hold '2024-03-15', '15/03/2024', 'March 15, 2024', or '2024-3-15' — all representing the same day, none of them comparable by < or > correctly, and none of them validated by the database engine.

Common string-date bugs to test:

Sorting: '2024-09-01' < '2024-10-01' is true alphabetically, but '2024-9-01' > '2024-10-01' is also true alphabetically (because '9' > '1').
Range queries: A BETWEEN on a string column may silently include or exclude records.
Locale mismatch: '03/04/2024' means March 4 in the US and April 3 in the UK.

Test cases should include inserting values in multiple formats and verifying the stored and retrieved values are identical and sortable correctly.

Implicit Conversion Traps

PostgreSQL will silently cast string literals to TIMESTAMP in many contexts:

SELECT * FROM orders WHERE created_at > '2024-01-01';
-- PostgreSQL casts the string to TIMESTAMP — no error, potentially wrong results
-- if session timezone differs from the intended timezone

The trap: if created_at is TIMESTAMPTZ and the session timezone is Europe/Kyiv (+02:00), the string '2024-01-01' is interpreted as '2024-01-01 00:00:00 Europe/Kyiv', which equals '2023-12-31 22:00:00 UTC'. Queries run in different timezones return different rows for the same literal.