PostgreSQL TO_CHAR() for Date

When you have a date value in your PostgreSQL table, you can change its format in different ways using TO_CHAR(). TO_CHAR() allows the users to convert the numeric values, timestamps, intervals, or integers to strings. For this post, we will focus on how to use PostgreSQL to convert the date format with TO_CHAR().

Working with TO_CHAR()

Whether you want to obtain the current date in your PostgreSQL query or work with dates in your table, understanding how to convert the date to a string is essential. Maybe you want a prettier format for the date as your output or to extract a section of the date after converting it to a string. Whatever the case, TO_CHAR() is the ideal function.

Besides, TO_CHAR() offers plenty of formatting options that you can use. You can combine the different options to get the desired output.

TO_CHAR() takes the following syntax:

TO_CHAR(expression, format);

The expression is the timestamp that you wish to convert using the specified format.

The following are the commonly used TO_CHAR() formats:

1. Year

YYYY – It shows the year in 4 digits.

Y,YYY – It uses a comma to represent the four digits in the year.

YYY – It only shows the last three digits in the specified year.

YY – It only shows the last two digits in the specified year.

Y – It only shows the last digit in the specified year.

2. Month

MONTH – It uses uppercase for the month name.

month – It uses lowercase for the month name.

MON – It abbreviates the month in uppercase.

Mon – It abbreviates and capitalizes the month.

MM – It only shows the month number.

3. Day

DAY – The uppercase day name.

day – The lowercase day name.

DY – It abbreviates the day name and capitalizes it.

Dy – It abbreviates and capitalizes the day name.

dy- The lowercase abbreviated day name.

4.  Time

HH – Hour of the day

HH12 –12-hour format

HH24 –24-hour format

MI – Minutes

SS – Seconds

The given formats are not the only TO_CHAR() formats that you can use, but they are the most commonly used ones. We will give their example usage in this post.

Example 1: Converting the Date to String

For this example, we type the target date as our expression and specify what format to convert it. The following output shows how we convert “2023-11-29” to a more readable and understandable string:

Example 2: Working with the Current Date

In PostgreSQL, the CURRENT_DATE gives you the date for that particular day.

Suppose we want to convert it to a string. We only need to use the CURRENT_DATE as our expression and then specify our format. You now get your current date as a string.

Still, you can change the format to a different one to meet your goal. For instance, if we only want to show the date, month, and year, we tune our command as follows:

The beauty of TO_CHAR() is that you can combine the different formats to create the final one that you wish to use for your date. Let’s move on and work with timestamps.

Example 3: Working with Timestamp

So far, we only worked with dates. However, if your date contains time, you can extract the time by specifying its ideal format.

Here’s an example where we specify to get the time in a 24-hour format from the provided timestamp, leaving out the date:

For the 12-hour time format, we use HH12 instead of HH24. Take a look at the following example:

Lastly, if we want to extract the date and time from the provided timestamp, we only need to add the ideal format that we wish to use. Here, we specify using HH12:MI:SS for the time and add a separator. Next, we specify to use “dd, Month, yyyy,” for the date.

Our final output is as follows:

Example 4: Working with a Table

All formats that we discussed and mentioned can be applied to a PostgreSQL table. For this example, we have a table named “orders” that contains the “date” column. To select the elements from it and use TO_CHAR() for the “date” column, we execute our command as illustrated in the following:

You can use any format that you wish. Here’s the same command but with a different date format:

If we only want to show the day of the week and the month from the date column, here’s how we tune the command:

Feel free to specify any ideal format that you want to work with for your case.


TO_CHAR() is a handy PostgreSQL function that lets the users convert the timestamps and other literals to strings. This post demonstrates the different ways where you can use TO_CHAR() for dates. We provided different examples to ensure that you quickly grasp the content. Hopefully, TO_CHAR() won’t trouble you anymore.

About the author

Denis Kariuki

Denis is a Computer Scientist with a passion for Networking and Cyber Security. I love the terminal, and using Linux is a hobby. I am passionate about sharing tips and ideas about Linux and computing.