Update only time in timestamp postgresql

Question

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;

eshirvana · Accepted Answer · 2023-03-03T14:59:06.397

9

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;

edited Mar 03 '23 at 14:59

answered Oct 07 '21 at 14:31

eshirvana

23,227
3
22
38

So I can only opdate it with intervals? I can't set a specific hour? – Thiago Salgado Oct 07 '21 at 14:35
1

@ThiagoSalgado see 3rd method – eshirvana Oct 07 '21 at 14:35
Oh, that's perfect, thanks a lot! – Thiago Salgado Oct 07 '21 at 14:36

Update only time in timestamp postgresql

1 Answers1