How to add a time to an existing timestamp in Postgresql/sql?-CodePudding

In my table, i changed the column from date to timestamp. At all the existing rows, Postgres added automatically a time to the date, for example:

2020-04-04 --> 2020-04-04 00:00:00

But i want it set to 09:00 (so: 2020-04-04 09:00:00) for all the existing rows. Does anybody know how to do that?

Here is my code:

    ALTER TABLE members ALTER COLUMN joined SET DATA TYPE timestamp;
    ALTER TABLE members ALTER COLUMN joined SET DEFAULT current_timestamp;
--for all existing columns, change timestamp to [entered-date_09:00:00]

CodePudding user response：

ALTER COLUMN .. SET DATA TYPE .. USING is what you're looking for:

ALTER TABLE members 
  ALTER COLUMN joined SET DATA TYPE timestamp USING joined   '09:00:00'::time;

Or using an UPDATE in case you already have a timestamp column

UPDATE members
SET joined = joined   '09:00:00'::time

Demo: db<>fiddle

CodePudding user response：

Add 9 hours:

update members
set joined = joined   interval '9' hour