Dates and times

How do you format a date in PostgreSQL?

By Owen Middleton · Updated September 2026 · Examples run on PostgreSQL 17

Use to_char(value, pattern): to_char(TIMESTAMP '2022-02-10 10:00', 'YYYY-MM-DD') returns the text 2022-02-10. PostgreSQL has no format(date, ...), because the first argument to format() is always the template string.

Run to_char and you get text back, so a label sorts by its characters and the year has to come first for that to match calendar order. Group and sort by a 'YYYY-MM' label and you get the same months in the same sequence as grouping by date_trunc('month', ...). Put the month first with 'Mon YYYY' and Apr leads, because it leads the twelve abbreviations as text. Drop the year with 'Mon' and every April merges into a single row. That is a wrong grouping, not a wrong order.

The queries below run against a single table, orders: one row per order, with ordered_at stored as a timestamp with time zone. This copy of it holds 38 months of orders starting in February 2022, which is where the dates in every result on this page come from. See also our other guides to SQL dates in PostgreSQL.

Schema · ecommerce1 table? = nullable

orders

idinteger

customer_id?integer

ordered_attimestamptz

statustext

total_amountnumeric

SELECT COUNT(*) AS rows,
       MIN(ordered_at)::date AS first_order,
       MAX(ordered_at)::date AS last_order
FROM orders;

rows	first_order	last_order
200	2022-02-10	2025-03-22

the shape of this copy of the orders table, which every result below is drawn from

What are the most common date formats in to_char?

For most reports and exports you need one of these four. Each column is one pattern applied to the same orders:

SELECT id,
       to_char(ordered_at, 'YYYY-MM-DD') AS iso,
       to_char(ordered_at, 'DD/MM/YYYY') AS day_first,
       to_char(ordered_at, 'FMMonth FMDD, YYYY') AS written_out,
       to_char(ordered_at, 'YYYY-MM-DD HH24:MI') AS with_time
FROM orders
ORDER BY id
LIMIT 3;

id	iso	day_first	written_out	with_time
1	2022-02-10	10/02/2022	February 10, 2022	2022-02-10 10:00
2	2022-02-15	15/02/2022	February 15, 2022	2022-02-15 14:30
3	2022-03-01	01/03/2022	March 1, 2022	2022-03-01 09:15

YYYY-MM-DD, DD/MM/YYYY, a written-out date, and a date with a 24-hour time

YYYY is the four-digit year, MM the month number and DD the day. Put FM in front of a part to remove its padding, which is why you see March 1 in the written-out column, not March followed by five spaces and 01.

Swap a pattern below and re-run it. Every result on this page was produced by running its query in PostgreSQL, with the session time zone set to UTC, and this editor is pinned to UTC too, so against your own database the times will differ: ordered_at is a timestamp with time zone, which PostgreSQL prints in your session time zone, so the 2022-02-10 10:00 above reads 05:00 in New York, 19:00 in Tokyo, and 2022-02-09 23:00 under Pacific/Midway, a day earlier. There are also two graded exercises at the end.

SELECT id,
       to_char(ordered_at, 'YYYY-MM-DD') AS iso,
       to_char(ordered_at, 'DD/MM/YYYY') AS day_first,
       to_char(ordered_at, 'FMMonth FMDD, YYYY') AS written_out,
       to_char(ordered_at, 'YYYY-MM-DD HH24:MI') AS with_time
FROM orders
ORDER BY id
LIMIT 3;

What happens if you use FORMAT() or DATE_FORMAT()?

You get an error from both. PostgreSQL does ship a format() function, but you use it to build a string from a template, and the first argument has to be that template. There is no format(date, ...), and ordered_at is a timestamp with time zone, for which there is no overload either:

SELECT FORMAT(ordered_at, 'yyyy-MM')
FROM orders;

PostgreSQL responds function format(timestamp with time zone, unknown) does not exist

PostgreSQL has no DATE_FORMAT at all:

SELECT DATE_FORMAT(ordered_at, '%Y-%m')
FROM orders;

PostgreSQL responds function date_format(timestamp with time zone, unknown) does not exist

Replace either one with to_char(), and translate the pattern. Microsoft writes that the format argument of SQL Server’s FORMAT “must contain a valid .NET Framework format string”, and defines yyyy as “The year as a four-digit number” and MM as “The month, from 01 to 12”. MySQL documents %Y as “Year, numeric, four digits” and %m as “Month, numeric (00..12)”. In PostgreSQL you write both of those as 'YYYY-MM'.

One more near miss: pass a quoted date straight to to_char and you fail. PostgreSQL reads a quoted literal as unknown, and it cannot pick between the eight to_char overloads:

SELECT to_char('2026-03-04', 'YYYY');

PostgreSQL responds function to_char(unknown, unknown) is not unique

Cast it first: to_char(DATE '2026-03-04', 'YYYY') or to_char('2026-03-04'::date, 'YYYY').

Why does my formatted time show the wrong minutes?

Because MM is the month in a to_char pattern, and minutes are MI. PostgreSQL raises no error for HH:MM, so the second field comes back as the month number and changes with the date rather than with the clock:

SELECT to_char(TIMESTAMP '2026-03-04 16:05', 'YYYY-MM-DD HH:MM') AS hh_mm,
       to_char(TIMESTAMP '2026-03-04 16:05', 'YYYY-MM-DD HH24:MI') AS hh24_mi,
       to_char(TIMESTAMP '2026-03-04 16:05', 'HH12:MI AM') AS twelve_hour;

hh_mm	hh24_mi	twelve_hour
2026-03-04 04:03	2026-03-04 16:05	04:05 PM

HH:MM printed the 12-hour hour and the month

Write HH:MM and two fields are wrong at once. HH is the hour on a 12-hour clock, so 16:05 gives 04. MM is the month, so the same timestamp in March gives 03 and in November it would give 11: the number looks like a plausible minute in every month of the year, which is what makes this one hard to spot. Use HH24:MI, or HH12:MI AM for a 12-hour time with its marker.

Why is there extra space after the month name?

Because PostgreSQL pads every name to nine characters for Month and Day, the length of September and Wednesday. You cannot see repeated spaces in a browser, so we show each one as a dot here:

SELECT replace(to_char(DATE '2026-05-04', 'Month YYYY'), ' ', '·') AS month,
       replace(to_char(DATE '2026-05-04', 'FMMonth YYYY'), ' ', '·') AS fm_month,
       length(to_char(DATE '2026-05-04', 'Month')) AS month_length;

month	fm_month	month_length
May·······2026	May·2026	9

spaces shown as dots; May is padded to nine characters

The padding is nine characters minus the length of the name, so May carries six spaces and March four. Any space you wrote in the pattern yourself sits on top of that, which is why 'Month YYYY' puts seven characters between May and the year, not six. Put FM in front of a part to remove the padding, and note that leading zeros go with it: FMDD gives 4 where DD gives 04. September and Wednesday are already nine characters long, so an example built on either of those names shows no padding at all.

Why are my formatted months in the wrong order?

Because to_char hands back text, and sorting a 'Mon YYYY' label sorts those characters rather than the dates behind them. Of the twelve month abbreviations Apr comes first as text, so every April in the table leads, whatever year it belongs to:

SELECT to_char(ordered_at, 'Mon YYYY') AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

month	orders
Apr 2022	2
Apr 2023	3
Apr 2024	6
Aug 2022	3
Aug 2023	5
Aug 2024	8
Dec 2022	2
Dec 2023	4
Dec 2024	5
Feb 2022	2

sorted alphabetically: every April, then every August · 28 more rows not shown

Every row is right and the order is useless. The label is a string, not a date:

SELECT pg_typeof(to_char(DATE '2026-03-04', 'YYYY-MM')) AS to_char_returns;

to_char_returns
text

Group and sort by a real date instead, and format only for display. Use date_trunc to round each timestamp down to the first of its month:

SELECT to_char(date_trunc('month', ordered_at), 'Mon YYYY') AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY date_trunc('month', ordered_at)
ORDER BY date_trunc('month', ordered_at);

month	orders
Feb 2022	2
Mar 2022	3
Apr 2022	2
May 2022	1
Jun 2022	3
Jul 2022	2
Aug 2022	3
Sep 2022	4
Oct 2022	2
Nov 2022	4

the same counts, in calendar order · 28 more rows not shown

We cover this pattern in depth in grouping by month, week or day. When you only need the order, take a shortcut: sort by the earliest timestamp in each group.

Try it: change ORDER BY 1 to ORDER BY min(ordered_at) and run it again. You get the months in calendar order.

SELECT to_char(ordered_at, 'Mon YYYY') AS month,
       COUNT(*) AS orders
FROM orders
GROUP BY 1
ORDER BY 1;

Which to_char patterns do you need?

Here are the sixteen you need for nearly every report. We apply each one to the same timestamp, 4 March 2026 at 16:05:09, and PostgreSQL prints text in double quotes, like "Q", as it is:

SELECT pattern,
       to_char(TIMESTAMP '2026-03-04 16:05:09', pattern) AS output
FROM (VALUES ('YYYY'), ('YY'), ('MM'), ('Mon'), ('FMMonth'), ('DD'), ('FMDD'),
             ('Dy'), ('FMDay'), ('HH24'), ('HH12'), ('MI'), ('SS'), ('AM'),
             ('"Q"Q'), ('IYYY-"W"IW')) AS p(pattern);

pattern	output
YYYY	2026
YY	26
MM	03
Mon	Mar
FMMonth	March
DD	04
FMDD	4
Dy	Wed
FMDay	Wednesday
HH24	16
HH12	04
MI	05
SS	09
AM	PM
"Q"Q	Q1
IYYY-"W"IW	2026-W10

one timestamp, sixteen patterns

PostgreSQL copies your capitalisation: you get Mar from Mon, MAR from MON, and mar from mon. You will find the full list in the PostgreSQL documentation on formatting functions. To go from text back to a date, use the same letters in to_date() and to_timestamp().

Practice: format dates for a report

You format months for display, and you group by date with the real value. That lesson is free, and so is the one it builds on, DATE_TRUNC and EXTRACT. No account, nothing to install, nothing to pay.