Often in PostgreSQL you may want to format a date field as DD/MM/YYYY.
You can use the to_char() function with the following syntax to do so:
SELECT *,
to_char(date_sold, 'DD/MM/YYYY') AS new_date
FROM products;
This particular example will create a new column named new_date that formats the date from the date_sold column using DD/MM/YYYY as the format.
The following example shows how to use this syntax in practice.
Example: How to Format Date as DD/MM/YYYY in PostgreSQL
Suppose that we create the following table named products that contains information about when various products were sold at some retail store.
-- create table named 'products'
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT,
date_sold DATE
);
-- add rows to the table
INSERT INTO products VALUES (0001, 'A0045BH', '2024-04-01');
INSERT INTO products VALUES (0002, 'A00933R', '2024-06-02');
INSERT INTO products VALUES (0003, 'BRRHHYL', '2024-07-03');
INSERT INTO products VALUES (0004, 'BR33503', '2024-09-04');
INSERT INTO products VALUES (0005, 'CHYYGG4', '2024-10-05');
INSERT INTO products VALUES (0006, 'C743559', '2024-11-06');
INSERT INTO products VALUES (0007, 'HTT56HH', '2024-12-07');
-- view all rows in table
SELECT * FROM products;
id | name | date_sold
----+---------+------------
1 | A0045BH | 2024-04-01
2 | A00933R | 2024-06-02
3 | BRRHHYL | 2024-07-03
4 | BR33503 | 2024-09-04
5 | CHYYGG4 | 2024-10-05
6 | C743559 | 2024-11-06
7 | HTT56HH | 2024-12-07
The dates in the date_sold column are currently formatted using YYYY-MM-DD.
Suppose instead that we would like to format the dates as DD/MM/YYYY.
We can use the following syntax to do so:
SELECT *,
to_char(date_sold, 'DD/MM/YYYY') AS new_date
FROM products;
id | name | date_sold | new_date
----+---------+------------+------------
1 | A0045BH | 2024-04-01 | 01/04/2024
2 | A00933R | 2024-06-02 | 02/06/2024
3 | BRRHHYL | 2024-07-03 | 03/07/2024
4 | BR33503 | 2024-09-04 | 04/09/2024
5 | CHYYGG4 | 2024-10-05 | 05/10/2024
6 | C743559 | 2024-11-06 | 06/11/2024
7 | HTT56HH | 2024-12-07 | 07/12/2024
Notice that the new column named new_date contains the dates from the date_sold column formatted as DD/MM/YYYY.
Feel free to use similar syntax with the to_char() function to format dates in your own table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Select Rows in Date Range
PostgreSQL: How to Select the Last Row per Group
PostgreSQL: How to Calculate Difference Between Rows
