Update only time in timestamp postgresql-CodePudding

I have to update a timestamp in my database, but I want to change only the time, keeping the once registered date, for example, in '2021-10-07 11:00:00' I want to update it to '2021-10-07 10:00:00', how can I accomplish that?

the query I tried to run:

update p_bab.registro_ponto
set data_hora = timestamp::'10:00:00'
where id = 50;

CodePudding user response：

update p_bab.registro_ponto
set data_hora = data_hora - interval '1 hour'
where id = 50;

if not and you have a constant hour to set then :

update p_bab.registro_ponto
set data_hora = date(data_hora)   interval '10 hour'
where id = 50;

update p_bab.registro_ponto
set data_hora = date(data_hora)   '10:30:23'::time
where id = 50;