In this PostgreSQL tutorial, I will demonstrate how to utilize PostgreSQL’s TO_CHAR format date, enabling you to understand how to use the TO_CHAR() function to format dates in a meaningful way. You will learn practical examples where I will show you how to use TO_CHAR() on a column of type date in the table, and also how to use the TO_CHAR() function with the INSERT statement.
PostgreSQL To_Char Date Format
The TO_CHAR function in PostgreSQL converts date, number, and timestamp values into strings to represent the information or data in a meaningful and understandable format. So how TO_CHAR function is very helpful.
- Consider a global company with numerous restaurants across different countries, which utilizes an app to track sales. People in the USA prefer dates in the format ‘month-day-year’, while those in the UK prefer dates in the format ‘day-month-year’.
- To maintain a country-specific date format and to accommodate people’s preferences for displaying dates, the TO_CHAR() function can be used to present the date in a different format.
- Essentially, it converts the date into a format according to the user’s preference or location, allowing everyone to view it in their preferred way.
- Let’s take one more example and understand how the TO_CHAR() function can be helpful. When two computers communicate with each other and belong to different regions, the TO_CHAR() function can convert data and numbers into a format that both can understand. It acts as a translator.
Now you know the importance of the TO_CHAR() function, let’s see how you can use it in PostgreSQL to format the date.
Syntax
The syntax is given below.
TO_CHAR(exp_value, format)- TO_CHAR: It is the function itself that formats values.
- exp_value: It is an expression that you pass to the TO_CHAR() function to convert it into a specific format.
- Format: It defines how you want to format the expression.
The output of the TO_CHAR() function is the text or string value of the provided expression. Let’s take an example to understand how to use the TO_CHAR() function to format a date.
In this tutorial, we will focus solely on date and time expressions.
Common date and time patterns or formats
Before beginning, you need to know some common date and time patterns or formats that you can use with the TO_CHAR() function to format the given date and time.
| Format Or Pattern | Meaning |
| D | It is a day of the week from 1 to 7. |
| DD | It is a day of the month from 01 to 31. |
| DY | It is a short day name such as Tue, Mon and Sun etc. |
| DAY | It is the full name of the day such as Monday, Sunday etc. |
| MM | It represents the month from 01 to 12. |
| M | It is a short name of the month such as Jan, Feb etc. |
| MON | It is the full name of the month such as January, February etc. |
| YYYY | It represents a 4-digit year, such as 2023 |
| YY | It represents the 2-digit year such as 23 |
| HH24 | It is the hour of the day from 00 to 24. |
| MI | It is the minutes from 00 to 59. |
| SS | It is the seconds from 00 to 59. |
Example 1: Basic Date Formatting
Let’s start with a simple example of how to use PostgreSQL to format dates using the TO_CHAR() function.
Format the date 2023-09-07 using the below query.
SELECT TO_CHAR('2023-09-07'::DATE, 'DD-MON-YYYY');
Look at how the date ‘2023-09-07’ is converted to the meaningful date format as 07-SEP-2023 in the above output. The TO_CHAR() takes the expression as ”2023-09-07′::DATE’, and the format as DD-MON-YYYY and converts it into the specified format.
The string ‘2023-09-07’ is converted to a date value using the type cast operator::DATE. If you want to know how to cast a string or a number into different data types in PostgreSQL, then refer to this tutorial, PostgreSQL Cast Int + Examples.
If you want to present the given date in a more understandable format, consider including the full-day name with the date ‘2023-09-07’. Use the below query.
SELECT TO_CHAR('2023-09-07'::DATE, 'Day, DD Month YYYY');
In the above query look at the format part ‘Day, DD Month YYYY’, where the pattern ‘Day’ is used to get the whole day name from the given date as in the output you can see the Thursday, 07 September 2023, this is more meaningful than the date in the previous example.
Example 2: Format Date in Table
Till now, you know how to use the TO_CHAR() function with the SELECT statement to format a given date, but here you are going to understand how to use the TO_CHAR() function with actual tables to communicate information in a meaningful way.
Suppose you work at an e-commerce platform, and you have been given a task to generate monthly sales reports. Company management wants you to present the sales reports in a detailed format, which means it should include the day of the week as well.
The sales table is given below.
Use the query below to generate sales reports with a detailed date format.
SELECT
TO_CHAR(sale_date, 'Day, DD-MON-YYYY') as FormattedDate,
SUM(sale_amount) as TotalSales
FROM
sales
GROUP BY
sale_date
ORDER BY
sale_date DESC;After executing the above query, I got the expected output as shown in the screenshot below.
Examine the output above and note how the date is formatted in detail, including the day of the week, for a monthly total sale of each product. For example, the total sales on Tuesday, 24-Jan-2023, are $ 310.45.
The query part TO_CHAR(sale_date, ‘Day, DD-MON-YYYY’) as FormattedDate converts the date of column sale_date in the format ‘Day, DD-MON-YYYY’, where the format ‘Day’ is the week name and ‘DD-MON-YYYY’ is the actual date.
Consider one more example, where you are given the task of generating an invoice number that should contain the year and month of invoice creation, followed by a sequence number. There is a table called ‘invoices’ that contains the invoices for all customers.
Suppose that whenever a customer makes a purchase, an invoice is generated and stored in the invoice table. Use the TO_CHAR() function with the INSERT statement, as shown in the query below.
INSERT INTO invoices (invoice_number, customer_id, amount) VALUES
(TO_CHAR(NOW(), 'YYMM') || '_002', 124, 300.00);
SELECT * FROM invoices;
The above insert statement created an invoice with the number 2309_002 of the customer with ID 124 and an amount equal to 300.00.
- In the query part (TO_CHAR(NOW(), ‘YYMM’) || ‘_002’), the TO_CHAR() function takes the current date using the NOW() function and formats it using the pattern ‘YYMM’ which outputs the date as 2309 where 23 and 09 is year and month respectively.
- And then uses the pipe || operator to append the string value ‘_002’, which is the sequence number, to that date. Finally, it generates the invoice number as 2309_002, which contains the current year and month, followed by the sequence number.
Now you know how to format the date in PostgreSQL using the TO_CHAR() function. You can use different formats with the date to achieve the desired date format.
Conclusion
In this PostgreSQL tutorial, you learned how to use the PostgreSQL TO_CHAR() function to format the date. Additionally, you learned how to use the TO_CHAR() function with a SELECT statement to format dates and how to apply it to a column of type date. Finally, you learned how to format the date value in the INSERT statement to insert meaningful information into the table.
You may also read:
- How to Find PostgreSQL DateDiff
- PostgreSQL DATE Functions with Examples
- PostgreSQL Date Add + Add days to date in PostgreSQL Examples
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.
