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

Question

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

Huh. In what world does subtracting time result in a timestamp in the future? You need to explain more about what the rationale is for this? Add as update to question. — Adrian Klaver
– Adrian Klaver, Commented Jun 16, 2022 at 15:51

Frank Heikens · Accepted Answer · 2022-06-16 14:22:47Z

2

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;

answered Jun 16, 2022 at 14:22

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

SQLLER Over a year ago

Thanks its works, I cannot mark it as BEST answer as I have less score

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related