January 12, 2024 By Matthew Rathbone

In the world of SQL databases, PostgreSQL is widely known for its robustness and rich set of features. One of these features is its strong support for date and timestamp types. In this guide, we’ll explore how to format dates in PostgreSQL, complete with code samples. This tutorial targets software engineers and other technically-inclined individuals who want to expand their PostgreSQL knowledge.

Understanding Date in PostgreSQL

Before diving into how to format dates, it’s critical to understand how dates are represented in PostgreSQL. PostgreSQL uses the “Date” data type to store the date. Here’s how to create a table with a DATE column.

CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  order_date DATE NOT NULL
);

Now, let’s insert some data into the orders table.

INSERT INTO orders (order_date) VALUES 
('2022-04-01'),
('2022-04-02'),
('2022-04-03');

PostgreSQL is smart, and can figure out a date from a string if you provide the date in ISO 6801 format.

How to Format Dates in PostgreSQL: Using the TO_CHAR function

The primary method to format dates in PostgreSQL is using the TO_CHAR function. TO_CHAR allows us to format date/time types into a string of a specific pattern. Here’s format usage:

TO_CHAR(date, format)

The full list of formatting string options is available on the PostgreSQL docs. Here are the formatting options as of Jan 2024:

Pattern Description
HH hour of day (01–12)
HH12 hour of day (01–12)
HH24 hour of day (00–23)
MI minute (00–59)
SS second (00–59)
MS millisecond (000–999)
US microsecond (000000–999999)
FF1 tenth of second (0–9)
FF2 hundredth of second (00–99)
FF3 millisecond (000–999)
FF4 tenth of a millisecond (0000–9999)
FF5 hundredth of a millisecond (00000–99999)
FF6 microsecond (000000–999999)
SSSS, SSSSS seconds past midnight (0–86399)
AM, am, PM or pm meridiem indicator (without periods)
A.M., a.m., P.M. or p.m. meridiem indicator (with periods)
Y,YYY year (4 or more digits) with comma
YYYY year (4 or more digits)
YYY last 3 digits of year
YY last 2 digits of year
Y last digit of year
IYYY ISO 8601 week-numbering year (4 or more digits)
IYY last 3 digits of ISO 8601 week-numbering year
IY last 2 digits of ISO 8601 week-numbering year
I last digit of ISO 8601 week-numbering year
BC, bc, AD or ad era indicator (without periods)
B.C., b.c., A.D. or a.d. era indicator (with periods)
MONTH full upper case month name (blank-padded to 9 chars)
Month full capitalized month name (blank-padded to 9 chars)
month full lower case month name (blank-padded to 9 chars)
MON abbreviated upper case month name (3 chars in English, localized lengths vary)
Mon abbreviated capitalized month name (3 chars in English, localized lengths vary)
mon abbreviated lower case month name (3 chars in English, localized lengths vary)
MM month number (01–12)
DAY full upper case day name (blank-padded to 9 chars)
Day full capitalized day name (blank-padded to 9 chars)
day full lower case day name (blank-padded to 9 chars)
DY abbreviated upper case day name (3 chars in English, localized lengths vary)
Dy abbreviated capitalized day name (3 chars in English, localized lengths vary)
dy abbreviated lower case day name (3 chars in English, localized lengths vary)
DDD day of year (001–366)
IDDD day of ISO 8601 week-numbering year (001–371; day 1 of the year is Monday of the first ISO week)
DD day of month (01–31)
D day of the week, Sunday (1) to Saturday (7)
ID ISO 8601 day of the week, Monday (1) to Sunday (7)
W week of month (1–5) (the first week starts on the first day of the month)
WW week number of year (1–53) (the first week starts on the first day of the year)
IW week number of ISO 8601 week-numbering year (01–53; the first Thursday of the year is in week 1)
CC century (2 digits) (the twenty-first century starts on 2001-01-01)
J Julian Date (integer days since November 24, 4714 BC at local midnight; see Section B.7)
Q quarter
RM month in upper case Roman numerals (I–XII; I=January)
rm month in lower case Roman numerals (i–xii; i=January)
TZ upper case time-zone abbreviation (only supported in to_char)
tz lower case time-zone abbreviation (only supported in to_char)
TZH time-zone hours
TZM time-zone minutes
OF time-zone offset from UTC (only supported in to_char)

Date formatting examples

Let’s start by converting our date into a simple, readable string. Here’s how you do it:

SELECT order_id, TO_CHAR(order_date, 'DD Mon YYYY') as formatted_date
FROM orders;

The output would look like:

order_id | formatted_date
———+—————-
1 | 13 Jan 2024
2 | 12 Jan 2024
3 | 11 Jan 2024
4 | 10 Jan 2024
5 | 09 Jan 2024

We can also extract just the part of the date:

SELECT order_id, TO_CHAR(order_date, 'Day') as day_of_week
FROM orders;

order_id | day_of_week
———+————-
1 | Friday
2 | Thursday
3 | Wednesday
4 | Tuesday
5 | Monday

We can even combine formats if we like:

SELECT order_id, TO_CHAR(order_date, 'Day, DD Mon YYYY') as detailed_date
FROM orders;

order_id | detailed_date
———+————————
1 | Friday, 13 Jan 2024
2 | Thursday, 12 Jan 2024
3 | Wednesday, 11 Jan 2024
4 | Tuesday, 10 Jan 2024
5 | Monday, 09 Jan 2024

EXTRACT function

The EXTRACT function provides easy access to all the possible parts of a date/time value, like year, month, day, etc.

SELECT order_id, EXTRACT(MONTH from order_date) as order_month
FROM orders;

order_id | order_month
———+————
1 | 1
2 | 1
3 | 1
4 | 1
5 | 1

That’s the basics of date formatting in PostgreSQL. These tools should give a strong foundation for handling date data. As with all things in programming, practice is key: try out these commands for yourself and see what else you can come up with. Happy coding!

You should really try running these queries in Beekeeper Studio. :-)

Beekeeper Studio Is A Free & Open Source Database GUI

Best SQL query & editor tool I have ever used. It provides everything I need to manage my database. - ⭐⭐⭐⭐⭐ Mit

Beekeeper Studio is fast, intuitive, and easy to use. Beekeeper supports loads of databases, and works great on Windows, Mac and Linux.

Beekeeper's Linux version is 100% full-featured, no cut corners, no feature compromises.