sqlpostgresqlpostgresql-13

How can I update the time in a column without affecting the date?

In my database I have this column of type DATE with a lot of dates stored in it. I converted it to DATETIME using timestamptz. But the time is 00:00:00.

I want to change all the time to be 09:00:00 i tried a lot with update and set but I just get errors.

UPDATE note
SET entered = entered + TIMESTAMP(DATE(entered), TIME('09:00:00');

The error message was the following:

ERROR: syntax error at or near "DATE" LINE 2: SET entered = entered + TIMESTAMP(DATE(entered), TIME('09:00... ^ SQL state: 42601 Character: 47

Can anyone help me change the time?

Solution

  • We can try truncating all timestamps to midnight, then adding 9 hours:

    UPDATE note
SET entered = DATE_TRUNC('day', entered) + interval '9' hour;