How to Format Dates in PostgreSQL
Last updated August 26, 2026 · By the SaturnSQL team
Use to_char(value, 'pattern') to format dates and timestamps as text. Common pattern parts: YYYY, MM, DD, HH24, MI, SS, Mon, Day, and FM to strip padding.
SELECT
to_char(created_at, 'YYYY-MM-DD') AS iso_date,
to_char(created_at, 'DD Mon YYYY') AS readable,
to_char(created_at, 'YYYY-MM-DD HH24:MI') AS with_time,
to_char(created_at, 'FMDay, FMDD FMMonth') AS long_form
FROM orders
LIMIT 5;to_char returns text, so use it only for display; for grouping or comparisons keep native date/timestamp types (e.g. date_trunc('month', created_at)). FM before a pattern removes the blank-padding Postgres adds to Day and Month names.
MM is months, MI is minutes
The single most common to_char mistake is writing HH24:MM and getting the month number where the minutes should be. Minutes are MI. The patterns are also case-sensitive in a way that matters for text: MON gives AUG, Mon gives Aug, and mon gives aug.
SELECT to_char(now(), 'YYYY-MM-DD HH24:MI') AS correct,
to_char(now(), 'YYYY-MM-DD HH24:MM') AS repeats_the_month;Formatting is for display, not for filtering
Wrapping a column in to_char to compare it against a string throws away the index on that column and forces a sequential scan. Compare dates as dates and format only in the final SELECT list. Note also that to_char on a timestamptz renders in the session time zone, so the same row can format differently for two users.
-- slow, ignores the index
SELECT * FROM orders WHERE to_char(created_at, 'YYYY-MM') = '2026-08';
-- fast, uses it
SELECT * FROM orders
WHERE created_at >= '2026-08-01' AND created_at < '2026-09-01';Common errors
ERROR: date/time field value out of range: "31/08/2026" HINT: Perhaps you need a different "datestyle" setting.
Postgres read the string with the session DateStyle, which is MDY by default, so it tried to make sense of month 31.
Parse the string with to_date and an explicit pattern rather than relying on a cast and a session setting.
SELECT to_date('31/08/2026', 'DD/MM/YYYY');Run this in SaturnSQL
SaturnSQL is a browser-based SQL editor for teams: shared query library, schema-aware autocomplete, and scheduled exports to Google Sheets and Slack.
Try it free