How to Format Dates in PostgreSQL

How do I format dates in PostgreSQL?

Convert DATE or TIMESTAMP values to human-readable text using format patterns.

Description

Why would I format dates in PostgreSQL?

Applications, reports, and logs often need dates shown in a user-friendly style (e.g., “14-Feb-2024” instead of “2024-02-14”). Formatting inside SQL lets you keep presentation logic close to the data and avoid extra code in the app layer.

What function formats dates?

Use TO_CHAR(). It accepts any date/time type and a format pattern string. Pattern tokens are case-sensitive, so YYYY differs from yyyy.

How do I format a DATE?

SELECT TO_CHAR(CURRENT_DATE, 'DD-Mon-YYYY'); -- 14-Feb-2024

Replace DD-Mon-YYYY with any combination of tokens (see below) to match the desired output.

Which format patterns are most useful?

YYYY – four-digit year
YY – two-digit year
MM – month number (01-12)
Mon/Month – abbreviated/full month name
DD – day of month
HH24/MI/SS – 24-hour time
TZ – time-zone abbreviation

How do I localize month and day names?

Set lc_time at session level: SET lc_time = 'de_DE'; TO_CHAR(NOW(), 'DD Mon YYYY') then returns German names.

Can I include literal text?

Enclose text in double quotes inside the pattern: TO_CHAR(NOW(), 'FM"Week:" IW, YYYY').

How do I handle time zones?

Convert the value first: SELECT TO_CHAR(NOW() AT TIME ZONE 'UTC', 'YYYY-MM-DD HH24:MI') AS utc_ts;

What about ISO 8601?

PostgreSQL already outputs ISO 8601 with to_json, but you can force it: TO_CHAR(ts, 'YYYY-MM-DD"T"HH24:MI:SSOF').

How do I pad or trim zeros?

Add FM (fill mode) at the start of the pattern to suppress leading blanks and zeros: TO_CHAR(DATE '2024-02-02', 'FMMon DD') → Feb 2.

Is the new `format()` function useful?

format() is for building strings. Combine it with to_char: SELECT format('Report date: %s', to_char(CURRENT_DATE, 'DD Mon YYYY'));

Best practices?

Keep raw columns as DATE/TIMESTAMP, perform formatting only in the final SELECT layer, and document patterns in code comments for maintainability.

Why How to Format Dates in PostgreSQL is important