In PostgreSQL, TO_DATE is for dates only, so when your string includes both date and time you must use TO_TIMESTAMP or a cast to a timestamp type instead.

TO_DATE vs TO_TIMESTAMP

TO_DATE(text, format) converts a string into a date, so any time portion in the input is ignored, even if the format mask includes time tokens like HH24:MI:SS. TO_TIMESTAMP(text, format) converts a string into a timestamp with time zone (or timestamptz), and correctly parses both date and time components according to the format string.

For typical ISO-like strings such as 2025-11-28 08:53:02, TO_TIMESTAMP(‘2025-11-28 08:53:02’, ‘YYYY-MM-DD HH24:MI:SS’) is the appropriate function because the value clearly contains time-of-day information.

Correct usage examples

To turn a full datetime string into a timestamp:

SELECT TO_TIMESTAMP(‘2025-11-28 08:53:02’, ‘YYYY-MM-DD HH24:MI:SS’);

This returns a timestamp value that you can use in comparisons, inserts, or calculations.

If you really only need the calendar date from a datetime-like string, either strip the time yourself or change the format so it does not include time tokens, for example:

SELECT TO_DATE(‘2025-11-28’, ‘YYYY-MM-DD’);

This yields a date with no time-of-day component.

Casting strings directly

For standard formats, simple casting is often cleaner than calling formatting functions. PostgreSQL can cast a literal string directly to a timestamp type:

SELECT ‘2025-11-28 08:53:02’::timestamp;

This uses PostgreSQL’s built-in input parser for timestamps and returns a timestamp without time zone.

You can do the same with CAST:

SELECT CAST(‘2025-11-28 08:53:02’ AS timestamp);

This is functionally equivalent and may be preferred for portability.

Converting a text column in a query

When the source data lives in a text (or varchar) column, use TO_TIMESTAMP with an explicit format mask in a SELECT, UPDATE, or ALTER TABLE … USING expression:

SELECT TO_TIMESTAMP(mycol, ‘YYYY-MM-DD HH24:MI:SS’) AS ts
FROM mytable;

This converts each row’s string into a proper timestamp, assuming the contents are consistently formatted. For a schema change, you might use:

ALTER TABLE mytable
ALTER COLUMN mycol TYPE timestamp
USING TO_TIMESTAMP(mycol, ‘YYYY-MM-DD HH24:MI:SS’);

which permanently converts the column to a real timestamp type.

Practical Examples: INSERT, WHERE, and ALTER TABLE

INSERT with TO_TIMESTAMP

When inserting a string that includes date and time and you want a real timestamp:

INSERT INTO mytable (id, created_at)
VALUES (
1,
TO_TIMESTAMP(‘2025-11-28 08:53:02’, ‘YYYY-MM-DD HH24:MI:SS’)
);

This converts the string into a proper timestamp as it is inserted.

If your source is already a column in another table:

INSERT INTO mytable (id, created_at)
SELECT id, TO_TIMESTAMP(created_at_text, ‘YYYY-MM-DD HH24:MI:SS’)
FROM staging_table;

This is common when importing from CSVs where datetimes are stored as text.

WHERE clause using TO_TIMESTAMP or cast

Filtering rows when the column is text and you want to compare by timestamp:

SELECT *
FROM mytable
WHERE TO_TIMESTAMP(mycol, ‘YYYY-MM-DD HH24:MI:SS’)
>= TO_TIMESTAMP(‘2025-11-28 00:00:00’, ‘YYYY-MM-DD HH24:MI:SS’);

This parses both sides as timestamps so you get correct chronological comparison instead of text ordering.

If mycol is already a timestamp column, you do not need TO_TIMESTAMP:

SELECT *
FROM mytable
WHERE mycol >= ‘2025-11-28 00:00:00’::timestamp;

PostgreSQL will cast the literal to timestamp for the comparison.

ALTER TABLE converting text → timestamp

To permanently convert a text column that holds YYYY-MM-DD HH24:MI:SS strings into a real timestamp:

ALTER TABLE mytable
ALTER COLUMN mycol
TYPE timestamp
USING TO_TIMESTAMP(mycol, ‘YYYY-MM-DD HH24:MI:SS’);

The USING clause tells PostgreSQL exactly how to transform existing text values during the type change.

If you actually want time zone–aware values, change the type to timestamptz:

ALTER TABLE mytable
ALTER COLUMN mycol
TYPE timestamptz
USING TO_TIMESTAMP(mycol, ‘YYYY-MM-DD HH24:MI:SS’);

This is useful when your text values should be interpreted in a specific time zone and stored with zone awareness.

When to use each approach

• Use TO_DATE only when:
• The string represents a pure calendar date, and
• You truly do not care about time-of-day.
• Use TO_TIMESTAMP when:
• The string includes both date and time and possibly a non-default format.
• Use ::timestamp or CAST(… AS timestamp) when:
• The string is already in a standard PostgreSQL timestamp format and you just need a straightforward type conversion.

If you share the exact use case (INSERT value, WHERE clause filter, ALTER TABLE conversion, etc.), a precise one-liner can be tailored to your scenario.