Search code examples
sqlpostgresqltimestampepoch

Compare string to datetime in postgres

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.

Solution

  • 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.