How to update hours in timestamp without effecting date in postgreSql?-CodePudding

I have two columns in_time and out_time, both are timestamps with timezone. When I try to update/change hours, date also effected, I only want to change hours without effecting date(Database:Postgresql)

update activity
    set in_time =  in_time - interval '5.5 hours',
        out_time = gout_time - interval '5.5 hours'
    where emp_id = 72;

Inputs

  in_time ==> 2022-02-03 19:30:00:000  530
  out_time ==> 2022-02-03 04:30:00:000  530

Output I am getting

   in_time ==> 2022-02-03 14:00:00:000  530
   out_time ==> 2022-02-02 23:00:00:000  530

Expected Output

   in_time ==> 2022-02-03 14:00:00:000  530
   out_time ==> 2022-02-03 23:00:00:000  530

CodePudding user response：

You have to CAST twice, first a DATE and second a TIME. Do the calculation on the TIME and add the TIME to the DATE to create a new TIMESTAMP:

UPDATE activity
    SET in_time =  CAST(login_time AS date)   (CAST(login_time AS time) - interval '5.5 hours'),
        out_time = CAST(logout_time AS date)   (CAST(logout_time AS time) - interval '5.5 hours')
    WHERE emp_id = 72;