CODEWITHSUNDEEP
postgresqldatetimeunix-timestamp
Aravinth
Aravinth

Reputation: 91

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

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;

Upvotes: 1

Views: 200

Answers (2)

Akhilesh Mishra
Akhilesh Mishra

Reputation: 6140

You can use below queries for your problem

alter table public."Operation" add column arrival timestamp;
update public."Operation" set arrival=to_timestamp(arrival_unix_seconds);


alter table public."Operation" add column departure timestamp;
update public."Operation" set departure=to_timestamp(departure_unix_seconds);

To calculate difference in minutes you can use following methods:

If you want to keep the columns departure_unix_seconds and arrival_unix_seconds then

select (departure_unix_seconds - arrival_unix_seconds)/60  "Difference" from public."Operation";

If you want to use newly created columns then

select extract( epoch from (departure - arrival))::integer/60  "Difference" from public."Operation";

Upvotes: 1

sticky bit
sticky bit

Reputation: 37482

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.

Upvotes: 2

Related Questions