sqlpostgresqlalter-tablealteralter-column

POSTGRESQL convert date to unix timestamp int

I have a table matches with a column deleted_date which is of type date, so its values look like 2020-12-30 right now. I want to change the type of this column from date to int so I can store unix timestamps instead.

I am trying to do so by running this command:

ALTER TABLE matches ALTER COLUMN deleted_date TYPE INT USING deleted_date::INT;

But running this command gives me the error:

 error: cannot cast type date to integer

Is there a way I can tell POSTGRESQL to just replace any deleted_by date values with a new empty 'null' integer var?

Solution

  • You can try below alter statement for your requirement:

    ALTER TABLE matches ALTER COLUMN deleted_date TYPE INT USING extract(epoch from deleted_date)::INT;