Compare string to datetime in postgres

Question

I've a table with Timestamp column

timestamp(character varying)
1700520221
1632322404

I want to convert these into date time and compare if these are 1 year old and delete them if they're older.

I tried to_timestamp() but that doesn't work on tables.

Erwin Brandstetter · Accepted Answer · 2022-11-21 13:33:07Z

1

Assuming your numbers are seconds since 1970-01-01 00:00:00 UTC, you can very well use to_timestamp() after casting to a numeric type:

DELETE FROM tbl
WHERE  to_timestamp(col::float) < now() - interval '1 year';

to_timestamp() takes double precision. Unlike with an untyped literal, an explicit cast is needed for type varchar.

If at all possible, convert your varchar column to the appropriate type timestamptz.

answered Nov 21, 2022 at 5:06

669k160 gold badges1.2k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1