Convert multiple columns to timestamp from unix seconds and add it to the existing table as two new columns in Postgresql

Question

I am new to postgresql, I am trying to convert two columns into timestamp from unix seconds and adding as new columns. But I am not sure how to correct this.

SELECT arrival_unix_seconds,departure_unix_seconds,to_timestamp(arrival_unix_seconds) as arrival  FROM public."Operation"

alter table add column arrival timestamp;

I can only do it for one column and just display the result but can't add it to the table.

Also want to find the time difference between the two resultant columns in minutes.

The data looks like,

arrival_unix_seconds    departure_unix_seconds
1619808731;             1619809039;
1619808082;             1619808711;
1619807810;             1619809705;
1619807573;             1619808556;
1619807394;             1619808623;

Answer 1

First alter the table and add the two columns.

ALTER TABLE public."Operation"
            ADD COLUMN arrival timestamp,
            ADD COLUMN departure timestamp;

Then use UPDATE to copy the converted timestamps into the new columns.

UPDATE public."Operation"
       SET arrival = to_timestamp(arrival_unix_seconds),
           departure = to_timestamp(departure_unix_seconds);

And, since you now have columns directly depending on other columns in the table, you should drop the old columns to normalize the table again.

ALTER TABLE public."Operation"
            DROP COLUMN arrival_unix_seconds,
            DROP COLUMN departure_unix_seconds;

You should also consider not to use case sensitive identifiers like table names. They just make things more complicated than necessary. And identifiers in the database don't need to be "pretty". That's a job for the presentation layer.