I currently have this query:

SET    next_update = now() at time zone 'utc' 
WHERE  next_update::timestamp < now() at time zone 'utc' - interval '1 day';

Which updates the column next_update if a day has passed since the last next_update was set. But in the case the value couldn't be updated (a day hasn't passed yet) How can I return the hours, minutes and seconds left for update?

  • return where, a function, a variable? – McNets Feb 25 '19 at 21:01
  • Returning the remaining time for update with those values to display on the Python program that does the queries. @McNets – Blastcore Feb 25 '19 at 21:03
  • Use a function that a) updates the table and returns for example 0 or b) do not update the table and returns the remaining time. – McNets Feb 25 '19 at 21:07
  • Do you have an additional predicate, for example the primary key? Otherwise, what should happen when there are several rows that is not update? – Lennart Feb 25 '19 at 21:21
  • 1
    I would check the number of rows updated, if 0, select the remaining time. – Lennart Feb 25 '19 at 21:28

If you want a single query, I suggest a data-modifying CTE like:

WITH cte(bound) AS (SELECT now() AT TIME ZONE 'UTC' - interval '1 day')
   , upd AS (
   UPDATE MyTable 
   SET    next_update = now() AT TIME ZONE 'UTC' 
   FROM   cte
   WHERE  next_update < cte.bound  -- why cast to timestamp?
SELECT *, next_update - cte.bound AS time_to_update
FROM   MyTable, cte
WHERE  next_update >= cte.bound;

The point being that UPDATE and SELECT see the same snapshot and SELECT won't return the newly updated rows. (The fist CTE named cte is just for convenience, so we don't have to repeat the calculation of the bound.)

time_to_update is an interval of the form 23:53:27.141289 - hours:minutes:seconds:µs. Negative if there can be future timestamps - and possibly with leading days then: '-26 days -23:58:35.25222'. Else it cannot be greater than 24h in this query.

Assuming next_update is type timestamp, though the casts in your UPDATE are inconclusive (contradictory). now() AT TIME ZONE 'UTC' produces a timestamp, makes sense if next_update is of that type. But then why next_update::timestamp? (And why update a column named "next_update" to now()? Does not seem to make sense.)

  • Yeah it was my mistake, next_update should be now() + 1 day, not just now(). – Blastcore Feb 26 '19 at 2:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.