POSTGRESQL convert date to unix timestamp int

Question

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?

Does this answer your question? convert date to integer in postgresql — astentx
– astentx, Commented Dec 31, 2020 at 6:39
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
– astentx, Commented Dec 31, 2020 at 6:43
Why? An integer is one of the worst possible datatypes for a date or timestamp. — Frank Heikens
– Frank Heikens, Commented Dec 31, 2020 at 15:08

Akhilesh Mishra · Accepted Answer · 2020-12-31 07:06:00Z

2

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;

answered Dec 31, 2020 at 7:06

Akhilesh Mishra

6,1503 gold badges20 silver badges37 bronze badges

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

Comments

user330315 · Accepted Answer · 2020-12-31 08:40:34Z

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

answered Dec 31, 2020 at 8:40

user330315

Collectives™ on Stack Overflow

POSTGRESQL convert date to unix timestamp int

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related