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

Question

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]

Pretty much the same as https://stackoverflow.com/questions/69767308 — , Oct 29 '21 at 12:24

score 2 · Accepted Answer · answered Oct 29 '21 at 12:22

2

Add 9 hours:

update members
set joined = joined + interval '9' hour

answered Oct 29 '21 at 12:22

jarlh

42,561
8
45
63

score 2 · Answer 2 · answered Oct 29 '21 at 12:24

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

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

2 Answers2