CODEWITHSUNDEEP
sqlpostgresqlpostgresql-13
H. Motsu
H. Motsu

Reputation: 117

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?

Upvotes: 0

Views: 845

Answers (3)

Prasan Shrivastav
Prasan Shrivastav

Reputation: 1

for ms sql

#try this instead

UPDATE note SET entered = convert(nvarchar,entered,23)+' '+convert(nvarchar,'09:00:00')

Upvotes: 0

Manuel Gallina
Manuel Gallina

Reputation: 3

You may try adding a time interval, like so

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520878

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

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

Upvotes: 3

Related Questions