0

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
H. Motsu
  • 87
  • 5
  • 13
  • Does this answer your question? [How to add a variable number of hours to a date in PostgreSQL?](https://stackoverflow.com/questions/15160207/how-to-add-a-variable-number-of-hours-to-a-date-in-postgresql) – PM 77-1 Oct 27 '21 at 16:08

3 Answers3

3

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

UPDATE note
SET entered = DATE_TRUNC('day', entered) + interval '9' hour;
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
0

You may try adding a time interval, like so

UPDATE note
SET entered = enetered + interval '9 hours';
Manuel Gallina
  • 3
  • 1
  • 2
  • Wouldn't this update **add** 9 hours to the timestamp instead of changing its time? ;) – Jim Jones Oct 27 '21 at 16:17
  • @JimJones Yes, you are right, it does add 9 hours, but in this case, with all values having time = '00:00:00', you can achieve the same result without additional format conversions. The accepted answer is a more general solution – Manuel Gallina Oct 28 '21 at 12:17
0

for ms sql

#try this instead

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

Prasan Shrivastav
  • 1
  • 1