How can i update the time in a coloumn without affecting the date?-CodePudding

so in my database i have this coloumn that is from the type DATE, there are already a lot of dates 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?

CodePudding user response：

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

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

CodePudding user response：

You may try adding a time interval, like so

UPDATE note
SET entered = enetered   interval '9 hours';