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 stackoverflow.com/questions/69767308 — user330315
– user330315, Commented Oct 29, 2021 at 12:24

Jim Jones · Accepted Answer · 2021-10-29 12:24:05Z

3

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

answered Oct 29, 2021 at 12:24

Jim Jones

19.9k3 gold badges44 silver badges51 bronze badges

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

Comments

jarlh · Accepted Answer · 2021-10-29 12:22:03Z

2

Add 9 hours:

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

answered Oct 29, 2021 at 12:22

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Collectives™ on Stack Overflow

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

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