Date Format Conversion

The world of databases and frontend devs often don’t agree on how dates should look. PostgreSQL can store dates as DATE, TIMESTAMP, or even TIMESTAMPTZ, but that format isn’t always what you want to show to users. For example, instead of the default 2023-10-01 12:30:45, designers might want to see 01 October 2023, 12:30. Sometimes you need to format a date for reports or an API.

To convert dates to string format and back in PostgreSQL, you’ve got the TO_CHAR() and TO_DATE() functions.

The TO_CHAR() Function

TO_CHAR() is your best buddy when you need to turn date/time data into a human-readable string. It takes a date or timestamp and formats it according to your template.

Syntax

TO_CHAR(value, format)

• value — the date or timestamp you want to convert.
• format — a string with the format template, how you want the date to look.

Format Examples

You can find the full list of formats in the official PostgreSQL docs.

Examples of TO_CHAR() Usage

Formatting a date for a report

SELECT TO_CHAR(NOW(), 'DD.MM.YYYY') AS formatted_date;
-- Result: '09.10.2023'

Showing time in 12-hour format

SELECT TO_CHAR(NOW(), 'HH12:MI AM') AS formatted_time;
-- Result: '03:45 PM'

Displaying month as a word

SELECT TO_CHAR(NOW(), 'Month') AS month_name;
-- Result: 'October '

Heads up: PostgreSQL adds a space at the end. That’s a feature, not a bug! To get rid of spaces, use the TRIM() function:

SELECT TRIM(TO_CHAR(NOW(), 'Month')) AS trimmed_month_name;

Making a custom format

SELECT TO_CHAR(NOW(), 'YYYY/MM/DD HH24:MI:SS') AS custom_format;
-- Result: '2023/10/09 15:45:30'

Formatting for user interface

SELECT TO_CHAR(NOW(), 'DD "October" YYYY year') AS user_friendly_date;
-- Result: '09 October 2023 year'

The TO_DATE() Function

TO_DATE() does the opposite: it takes a string and converts it to a DATE type. Why would you need this? For example, a user might enter a date like 01-10-2023, and PostgreSQL needs to “understand” what date that is.

Syntax

TO_DATE(value, format)

• value — a string containing the date.
• format — a string with the template describing the string format.

Examples of TO_DATE() Usage

Converting a string to a date

SELECT TO_DATE('01-10-2023', 'DD-MM-YYYY') AS date_value;
-- Result: '2023-10-01' (data type: DATE)

Comparing a string date with a date in a table

Let’s say we have a table appointments with a column appointment_date of type DATE. The user enters a date as a string:

SELECT *
FROM appointments
WHERE appointment_date = TO_DATE('2023-10-09', 'YYYY-MM-DD');

Wrong format

Important: if the string format doesn’t match the template, you’ll get an error! For example:

SELECT TO_DATE('01/10/2023', 'DD-MM-YYYY');
-- Error: invalid input format

Checking user input

Let’s say we’re making a table to store orders, where the date is entered by the user:

CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    order_date DATE
);

-- Inserting data with string-to-date conversion
INSERT INTO orders (order_date)
VALUES (TO_DATE('10-09-2023', 'MM-DD-YYYY'));

Practical Examples

Formatting a report. The sales table stores sale dates in the sale_date column (type TIMESTAMP). You need to make a report where dates are in DD.MM.YYYY format.

-- Sample data
CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date TIMESTAMP
);

INSERT INTO sales (sale_date)
VALUES
    ('2023-10-01 15:30:00'),
    ('2023-10-02 10:15:00'),
    ('2023-10-03 12:45:00');

-- Report
SELECT sale_id,
       TO_CHAR(sale_date, 'DD.MM.YYYY') AS formatted_date
FROM sales;

Converting user input. Let’s say the user enters a date as a string in MM/DD/YYYY format. You need to convert it to DATE to save it in the system.

INSERT INTO sales (sale_date)
VALUES (TO_TIMESTAMP('10/01/2023 15:30:00', 'MM/DD/YYYY HH24:MI:SS'));

Common Mistakes and Tips

Wrong format. A common mistake is when the string format doesn’t match the template. For example, if the user entered 01-10-2023 but the format is MM/DD/YYYY, PostgreSQL will throw an error. Tip: always validate user input before sending it to SQL.

Spaces in TO_CHAR() formats. Some formats, like MONTH, add spaces. If that’s a problem, use the TRIM() function.

String parsing errors. If the string has unexpected characters or format, PostgreSQL won’t be able to convert it. Tip: use regex or extra data checks before inserting into the database.

Incorrect use of time formats. For example, trying to handle a TIMESTAMP with a template for DATE. Tip: make sure your data types match what you’re trying to do.

The TO_CHAR() and TO_DATE() functions open up tons of possibilities for working with date/time data. You can make user-friendly formats for reports, convert user input, and make your SQL queries way more readable. In real life, these functions are used all the time for data visualization, reporting, integrating with other systems, and building user interfaces.