Update row if column value is older than 1 hour-CodePudding

I have this table in PostgreSQL:

CREATE TABLE pet (
_id SERIAL PRIMARY KEY,
player_id int REFERENCES player(_id),
feed_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
num_poop integer DEFAULT 0,
clean_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
play_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
health integer DEFAULT 100
);

I want to make separate update queries for clean_time, play_time, or feed_time and update each with the current timestamp only if the existing value is more than an hour in the past (according to the current time of the database).
If I am able to update, I want to add 20 to the current health value without exceeding 100.

I can do this by selecting from the database, doing all conditional logic in Javascript, and updating. But can I achieve this in a single UPDATE query to PostgreSQL?

CodePudding user response：

UPDATE pet
SET    last_cleaned = CURRENT_TIMESTAMP
     , health = LEAST(health   20, 100)
WHERE  _id = 1
AND    last_cleaned < CURRENT_TIMESTAMP - interval '1 hour';

Besides being simpler and shorter, the filter on last_cleaned is also "sargable", i.e. can use an index.

Aside: it's "last_cleaned" in your answer, but "clean_time" in the question.

CodePudding user response：

The query below worked:

UPDATE pet
SET last_cleaned = CURRENT_TIMESTAMP, health = LEAST(health 20, 100)
WHERE _id=1 AND
  CASE
    WHEN age(current_timestamp, last_cleaned) > interval '1 hour' THEN true
    ELSE false
  END;