(opens in a new tab)

Postgres Date Format: TO_CHAR Patterns & Examples

August 23, 2026 by Chat2DB Team

PostgreSQL has exactly one function you need for turning a date, timestamp or timestamptz into human-readable text: TO_CHAR(value, pattern). Everything else (the DateStyle setting, casts to text, DATE_TRUNC, AT TIME ZONE) decides which moment you are formatting and how the server prints values when you do not ask for a pattern. This guide covers the Postgres date format toolbox end to end: the default output formats, a full table of the useful TO_CHAR template patterns, the FM and TH modifiers, ready-made formats (ISO 8601, US, European, RFC 2822-style, year-month), casting a timestamp to a date, parsing with TO_DATE/TO_TIMESTAMP and their traps, time zones, locale, and the two classic mistakes: sorting formatted text and indexing TO_CHAR expressions.

Everything is valid for PostgreSQL 14 through 17. Run the examples with SET timezone = 'UTC'; so your output matches the expected results shown here.

Sample data

CREATE TABLE events (
  id          bigserial PRIMARY KEY,
  name        text NOT NULL,
  occurred_at timestamptz NOT NULL
);
 
INSERT INTO events (name, occurred_at) VALUES
  ('signup',  '2026-01-05 08:15:30.123456+00'),
  ('upgrade', '2026-03-09 17:45:00+00'),
  ('renewal', '2026-08-23 23:59:59.5+00');
 
-- a bigger table for grouping/index examples
CREATE TABLE page_views (
  id        bigserial PRIMARY KEY,
  viewed_at timestamptz NOT NULL
);
INSERT INTO page_views (viewed_at)
SELECT timestamptz '2026-01-01 00:00+00' + g * interval '7 minutes'
FROM generate_series(1, 50000) AS g;

Default output formats and DateStyle

When you SELECT a date/time column without formatting, the server renders it according to the DateStyle parameter. The default is ISO, MDY:

SHOW datestyle;                       -- ISO, MDY
SELECT occurred_at, occurred_at::date, occurred_at::time FROM events WHERE id = 1;

DateStyle has two parts: the output style (ISO, SQL, Postgres, German) and the field order (MDY, DMY, YMD), which controls both output of the non-ISO styles and input parsing of ambiguous strings:

SET datestyle = 'SQL, DMY';
SELECT date '2026-08-23';              -- 23/08/2026
SELECT date '03/09/2026';              -- 2026-09-03  (DMY: 3 September!)
 
SET datestyle = 'German';
SELECT date '2026-08-23';              -- 23.08.2026
 
SET datestyle = 'ISO, MDY';            -- back to default
SELECT date '03/09/2026';              -- 2026-03-09  (MDY: March 9)

DateStyle is a session (or per-role/per-database) setting, so relying on it for presentation is fragile: a different client with a different default prints different text. Use it only to make ambiguous input unambiguous, and use TO_CHAR for output.

TO_CHAR(timestamp, pattern)

TO_CHAR takes a timestamp, timestamptz or interval (a date is implicitly cast to timestamp) and a template string, and returns text. Every letter sequence that matches a template pattern is replaced; anything inside double quotes is emitted literally; other characters (-, /, :, ., spaces) pass through unchanged.

SELECT name,
       to_char(occurred_at, 'YYYY-MM-DD HH24:MI:SS')        AS iso_like,
       to_char(occurred_at, 'FMDay, DD Mon YYYY')           AS friendly,
       to_char(occurred_at, '"Q"Q YYYY')                    AS quarter
FROM events ORDER BY id;

Template pattern reference

All examples use timestamptz '2026-08-23 23:59:59.5+00' (a Sunday) with the session time zone set to UTC.

A few things to remember:

• Capitalization of the pattern drives capitalization of the output: Month gives August, MONTH gives AUGUST, month gives august.
• Month and Day are padded with spaces to 9 characters so columns line up in fixed-width output. That padding is almost never what you want in an application; see FM below.
• TZ and OF only carry real information for timestamptz. For a plain timestamp, TZ is empty and OF is always +00.
• WW and IW differ. WW is a naive "day-of-year divided by 7" week; IW follows ISO 8601 (the week containing the year's first Thursday is week 1, and early January can belong to week 52/53 of the previous ISO year). Always pair IW with IYYY, never with YYYY.
• Use double quotes for literal text that might collide with patterns: '"Day" DD' prints Day 23, whereas 'Day DD' prints Sunday 23.

FM, TH/th and other modifiers

Modifiers attach to a single pattern:

• FM (fill mode) suppresses leading zeros and trailing padding for the pattern that follows it. It must be repeated for each pattern you want trimmed.
• TH / th append an uppercase / lowercase ordinal suffix.
• FX (fixed format) makes parsing strict; see TO_DATE below.
• TM (translation mode) prints month and day names in the lc_time locale; see the locale section.

SELECT to_char(occurred_at, 'Month DD, YYYY')          AS padded,
       to_char(occurred_at, 'FMMonth FMDD, YYYY')      AS trimmed,
       to_char(occurred_at, 'FMMonth DDth, YYYY')      AS ordinal,
       to_char(occurred_at, 'FMHH12:MI AM')            AS clock12
FROM events WHERE id = 3;

Note the padded column: Month produces August plus three spaces, then the literal space, then 23. That extra whitespace is the single most common TO_CHAR surprise.

Common formats cookbook

SELECT
  to_char(occurred_at, 'YYYY-MM-DD"T"HH24:MI:SSOF')         AS iso_8601,        -- 2026-08-23T23:59:59+00
  to_char(occurred_at, 'YYYY-MM-DD"T"HH24:MI:SS.MSTZH:TZM')  AS iso_8601_ms,     -- 2026-08-23T23:59:59.500+00:00
  to_char(occurred_at, 'MM/DD/YYYY')                        AS us,              -- 08/23/2026
  to_char(occurred_at, 'DD.MM.YYYY')                        AS european,        -- 23.08.2026
  to_char(occurred_at, 'Dy, DD Mon YYYY HH24:MI:SS TZHTZM') AS rfc_2822_style,  -- Sun, 23 Aug 2026 23:59:59 +0000
  to_char(occurred_at, 'YYYY-MM')                           AS year_month,      -- 2026-08
  to_char(occurred_at, 'IYYY-"W"IW')                        AS iso_week,        -- 2026-W34
  to_char(occurred_at, 'FMDay, FMMonth FMDDth YYYY')        AS long_form        -- Sunday, August 23rd 2026
FROM events WHERE id = 3;

Two details worth knowing: OF prints +00, not +00:00, so use TZH:TZM when a consumer requires the colon form; and the RFC 2822 example is only "style" because the day/month names come out in English regardless of locale, which is actually what RFC 2822 requires.

TO_CHAR also formats intervals, which is handy for durations:

SELECT to_char(interval '95 minutes 30 seconds', 'HH24:MI:SS');   -- 01:35:30
SELECT to_char(now() - occurred_at, 'DD "days" HH24 "hours"') FROM events WHERE id = 1;

Casting a timestamp to a date

Formatting is not the same as truncating. If you need a date value (to group by, compare or store), cast or truncate instead of producing text:

SELECT occurred_at::date                      AS d,          -- date
       CAST(occurred_at AS date)              AS d_standard, -- same, SQL-standard syntax
       date_trunc('day', occurred_at)         AS day_start,  -- timestamptz 2026-08-23 00:00:00+00
       date_trunc('month', occurred_at)       AS month_start -- timestamptz 2026-08-01 00:00:00+00
FROM events WHERE id = 3;

For a timestamptz, both the ::date cast and date_trunc are evaluated in the session time zone. The renewal event at 23:59:59.5+00 becomes 2026-08-24 for a client whose timezone is Asia/Tokyo. If the business day is defined in a fixed zone, say so explicitly:

SELECT (occurred_at AT TIME ZONE 'America/New_York')::date   AS ny_date,
       date_trunc('day', occurred_at, 'America/New_York')    AS ny_day_start   -- 3-arg form, PG 12+
FROM events WHERE id = 3;
-- ny_date = 2026-08-23, ny_day_start = 2026-08-23 04:00:00+00 (midnight in New York, shown in the UTC session)

For reporting, group on the truncated value and format only at the end:

SELECT to_char(date_trunc('month', viewed_at), 'Mon YYYY') AS month, count(*)
FROM page_views
GROUP BY date_trunc('month', viewed_at)
ORDER BY date_trunc('month', viewed_at);

Grouping by date_trunc instead of to_char(viewed_at, 'YYYY-MM') keeps the grouping key a real timestamptz, which sorts chronologically and can be compared with other timestamps.

TO_DATE and TO_TIMESTAMP: parsing, and their pitfalls

TO_DATE(text, pattern) returns a date; TO_TIMESTAMP(text, pattern) returns a timestamptz interpreted in the session time zone (there is also TO_TIMESTAMP(double precision) for Unix epochs). The same template patterns are used in reverse.

SELECT to_date('23/08/2026', 'DD/MM/YYYY');                          -- 2026-08-23
SELECT to_timestamp('2026-08-23 11:59 PM', 'YYYY-MM-DD HH12:MI AM'); -- 2026-08-23 23:59:00+00
SELECT to_timestamp(1787529599);                                     -- 2026-08-23 23:59:59+00

Pitfalls, in order of how often they bite:

1. Lenient matching without FX. By default, separators and whitespace in the input do not have to match the template, and trailing text is ignored. to_date('2026/08/23 junk', 'YYYY-MM-DD') returns 2026-08-23 without complaint. Prefix the template with FX to make the match strict: to_date('2026/08/23', 'FXYYYY-MM-DD') raises an error.
2. Out-of-range fields. Since PostgreSQL 10, to_date('2026-02-30', 'YYYY-MM-DD') raises date/time field value out of range. On very old servers it silently rolled over to March 2; if you inherited code that relied on that, it will now fail.
3. Two-digit years. YY resolves to the year nearest 2020: values below 70 become 20xx, 70 and above become 19xx. to_date('69-01-01', 'YY-MM-DD') is 2069; '70-01-01' is 1970. Use YYYY wherever you control the data.
4. Time zone. TO_TIMESTAMP has no idea what zone the string is in unless the pattern includes TZH/TZM (or OF); otherwise it applies the session timezone. The same string parses to different instants on different clients.
5. Ambiguous mixes like YYYYMMDD with missing zeros. Non-FX mode reads to_date('2026823', 'YYYYMMDD') as 2026-82-3 and errors. Zero-pad the input or use separators.

If the input is already ISO 8601, skip TO_DATE entirely: '2026-08-23'::date and '2026-08-23T23:59:59Z'::timestamptz are validated by the type input routine and are faster.

Formatting timestamptz in a specific time zone

AT TIME ZONE converts a timestamptz into a wall-clock timestamp in the named zone. Format that, or set the session zone so TO_CHAR sees the right offset:

-- Option 1: convert, then format (TZ/OF will be blank/+00 because the result is a plain timestamp)
SELECT to_char(occurred_at AT TIME ZONE 'Europe/Berlin', 'YYYY-MM-DD HH24:MI') FROM events WHERE id = 3;
-- 2026-08-24 01:59
 
-- Option 2: set the session zone, keep the offset available
SET timezone = 'America/Los_Angeles';
SELECT to_char(occurred_at, 'YYYY-MM-DD HH24:MI TZ (OF)') FROM events WHERE id = 3;
-- 2026-08-23 16:59 PDT (-07)
SET timezone = 'UTC';

Use full IANA names (Europe/Berlin), not abbreviations (CET), so daylight-saving transitions are handled correctly.

Locale: lc_time and the TM modifier

Month, Mon, Day and Dy are English by default. The TM modifier switches to the names of the lc_time locale, which you can change per session if the locale is installed on the server OS:

SET lc_time = 'de_DE.utf8';
SELECT to_char(occurred_at, 'TMDay, DD. TMMonth YYYY') FROM events WHERE id = 3;
-- Sonntag, 23. August 2026
SET lc_time = 'fr_FR.utf8';
SELECT to_char(occurred_at, 'TMDay DD TMMonth YYYY') FROM events WHERE id = 3;
-- dimanche 23 août 2026

Without TM, lc_time has no effect at all. Note that TM also implies FM-style trimming of padded names.

Format in the presentation layer, keep columns as timestamptz

Doing all formatting in SQL is tempting, but there are good reasons to push it outward:

• Storage and indexing. Store instants as timestamptz. A B-tree index on the column serves range queries (WHERE viewed_at >= '2026-08-01' AND viewed_at < '2026-09-01') directly. An expression like to_char(viewed_at, 'YYYY-MM') = '2026-08' cannot use that index and scans the whole table.
• TO_CHAR is not IMMUTABLE. Because its output depends on lc_time and timezone, it is marked STABLE, so CREATE INDEX ON page_views ((to_char(viewed_at, 'YYYY-MM'))) fails with functions in index expression must be marked IMMUTABLE. If you truly need a derived bucket column, use an immutable expression such as date_trunc('month', viewed_at AT TIME ZONE 'UTC') or a generated column filled by a trigger.
• Locale and zone belong to the user, not the database. The same row may be shown to a user in Tokyo and one in Berlin. Your application (or the driver's date type plus Intl.DateTimeFormat, strftime, Java's DateTimeFormatter) knows the viewer's preferences; the SQL session does not.
• Text cannot be compared chronologically. Once a value is text, >/<, BETWEEN and date arithmetic no longer work.

TO_CHAR is still the right tool for ad-hoc queries, exports (CSV, reports, emails generated in SQL) and for building stable keys such as 'INV-' || to_char(d, 'YYYYMM').

Sorting pitfall when formatting to text

-- WRONG: sorts as text, so 01/02/2026 (Jan 2, 2026) sorts before 12/31/2025
SELECT to_char(occurred_at, 'MM/DD/YYYY') AS d FROM events ORDER BY d;
 
-- RIGHT: sort on the real column, display the formatted one
SELECT to_char(occurred_at, 'MM/DD/YYYY') AS d FROM events ORDER BY occurred_at;

ORDER BY on the alias sorts lexically. Only YYYY-MM-DD-style (big-endian, zero-padded) formats happen to sort correctly as text, and even those break as soon as you add FM or a month name. Always order by the underlying timestamptz.

When experimenting with templates, it helps to see the output side by side with the pattern. Chat2DB's free TO_CHAR format builder at https://chat2db.ai/tools/postgres-to-char-format-builder (opens in a new tab) lets you compose patterns and preview the result before pasting the final TO_CHAR call into your query; the desktop and web versions of Chat2DB (https://app.chat2db.ai (opens in a new tab)) run the examples above against any PostgreSQL 14-17 server.

FAQ

How do I format a Postgres timestamp as a date string without the time part?

Use to_char(ts, 'YYYY-MM-DD') for text, or ts::date if you need an actual date value (for grouping, comparison or storage). For a timestamptz, both are evaluated in the session time zone; add AT TIME ZONE 'Your/Zone' first when the business day is defined in a fixed zone.

Why does TO_CHAR put extra spaces after the month or day name?

Month and Day are blank-padded to 9 characters by design, so August becomes August . Prefix the pattern with FM (FMMonth, FMDay) to remove the padding, and remember that FM only applies to the single pattern that follows it.

Can I create an index on a TO_CHAR expression?

No. to_char is STABLE, not IMMUTABLE, because its result depends on lc_time and timezone, so PostgreSQL rejects it in index expressions and generated columns. Index the timestamptz column itself and filter with range predicates, or index an immutable expression such as date_trunc('day', ts AT TIME ZONE 'UTC').

Conclusion

The Postgres date format story is short once you separate the concerns. DateStyle controls the default rendering and how ambiguous input is parsed; TO_CHAR with patterns such as YYYY, MM, DD, HH24, MI, SS, Mon, Day, IW, TZ and OF, plus the FM and TH modifiers, produces any text layout you need; ::date, DATE_TRUNC and AT TIME ZONE change the value rather than its appearance; and TO_DATE/TO_TIMESTAMP parse text back, with FX when you need strictness. Keep columns as timestamptz, sort and index on the real value, and format as late as possible, ideally in the presentation layer, and you will avoid the whitespace, time zone and sorting surprises that generate most of the Stack Overflow questions on this topic.