How to subtract a numeric value stored in a column from a timestamp in postgres?

Question

I'm trying to subtract a numeric value from a timestamp without time zone in postgresql.

I have a table with the columns start_date, end_date and hour_spent. start_date and end_date are timestamps without time zone and hour_spent is numeric (10,2).

When I try, I get the error: operator does not exist: timestamp without time zone - numeric

hour_spent contains values such as 1.00, 2.50, 3.75 etc.

I've tried to cast hour_spent but get a different error. I've seen examples with 'interval' but I can't seem to use that either

UPDATE table
SET start_date = end_date - hour_spent
WHERE start_date IS NULL;

I expect the start_date to equal the end_date - the number of hours the hour_spent column represents

user330315 · Accepted Answer · 2019-10-30 14:45:41Z

1

You can use make_interval() and convert the hours to seconds:

UPDATE table
   SET start_date = end_date - make_interval(secs => hour_spent * 24 * 60)
WHERE start_date IS NULL;

answered Oct 30, 2019 at 14:45

user330315

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

Collectives™ on Stack Overflow

How to subtract a numeric value stored in a column from a timestamp in postgres?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related