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