CODEWITHSUNDEEP
sqlpostgresql
SQLLER
SQLLER

Reputation: 31

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

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

Upvotes: 1

Views: 1500

Answers (1)

Frank Heikens
Frank Heikens

Reputation: 126971

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;

Upvotes: 1

Related Questions