TO_DATE and TO_TIMESTAMP for parsing in PostgreSQL

Introduction

TO_DATE and TO_TIMESTAMP help turn text into dates or timestamps so the database can understand and work with them.

You get a date or time as text from a user or file and want to save it properly.

You want to compare or calculate with dates or times stored as text.

You need to sort or filter data by date or time but the data is in string form.

You want to convert different date formats into a standard date or timestamp.

You want to extract parts like year or month from a text date.

Syntax

PostgreSQL

TO_DATE(text, format)
TO_TIMESTAMP(text, format)

The text is the string you want to convert.

The format tells how the date or time is written in the text, like 'YYYY-MM-DD' or 'DD/MM/YYYY HH24:MI:SS'.

Examples

Converts a text date in year-month-day format to a date.

PostgreSQL

SELECT TO_DATE('2024-06-15', 'YYYY-MM-DD');

Converts a text with date and time to a timestamp.

PostgreSQL

SELECT TO_TIMESTAMP('15/06/2024 14:30:00', 'DD/MM/YYYY HH24:MI:SS');

Converts a text date in month-day-year format to a date.

PostgreSQL

SELECT TO_DATE('06-15-2024', 'MM-DD-YYYY');

Sample Program

This query converts a text date and a text timestamp into proper date and timestamp types.

PostgreSQL

SELECT TO_DATE('2024-12-01', 'YYYY-MM-DD') AS parsed_date,
       TO_TIMESTAMP('01/12/2024 18:45:30', 'DD/MM/YYYY HH24:MI:SS') AS parsed_timestamp;

Important Notes

Make sure the format string exactly matches the text format, or the conversion will fail.

TO_DATE returns a date without time, TO_TIMESTAMP returns date and time.

Use uppercase format codes like YYYY, MM, DD, HH24, MI, SS for clarity.

Summary

TO_DATE and TO_TIMESTAMP convert text into date or timestamp types.

You must provide the correct format string matching the text.

These functions help work with dates and times stored as text.