Search code examples
sqlpostgresqlsql-timestamp

Update only time in timestamp postgresql

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;
Solution 
  • update p_bab.registro_ponto
set data_hora = data_hora - interval '1 hour'
where id = 50;

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

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

    or

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