Guide to Formatting Dates in PostgreSQL

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:

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. :-)