I want to subtract the value in column lag_days from current_date. Usually I would do current_date - interval '1 day'

I'm getting syntax error on the following example to subtract by days:

dkarchive=> select current_date - interval lag_days day from example_tbl;
ERROR:  syntax error at or near "day"
LINE 1: select current_date - interval lag_days day from example_tb...

You don't need to use the interval syntax to begin with. You can simply subtract an integer from a date value, e.g. current_date - 1 is "yesterday".

select current_date - lag_days
from the_table;

If you do want to use an interval, the make_interval() function is quite useful for this:

select current_date - make_interval(days => lag_days)
from the_table;

There is however a difference between the two queries: the first one returns a date value, the second one a timestamp value.

  • You can also do select current_date - interval '1 day' * lag_days. – pensnarik Aug 3 '20 at 15:22

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.