1

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?

Martin
  • 1,336
  • 4
  • 32
  • 69
  • Does this answer your question? [convert date to integer in postgresql](https://stackoverflow.com/questions/28557770/convert-date-to-integer-in-postgresql) – astentx Dec 31 '20 at 06:39
  • 1
    Also please note, that *its value looks like* has nothing about datatype or storage. It's a display task and you should always care of this task only at display time at application side. – astentx Dec 31 '20 at 06:43
  • Why? An integer is one of the worst possible datatypes for a date or timestamp. – Frank Heikens Dec 31 '20 at 15:08

2 Answers2

2

If you want to store a timestamp (date + time) you should convert the column to a timestamp or even better timestamptz type - do not use integers to store timestamps.

alter table matches alter deleted_date type timestamp
0

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;
Akhilesh Mishra
  • 5,876
  • 3
  • 16
  • 32