CODEWITHSUNDEEP
postgresql
SNpn
SNpn

Reputation: 2207

Postgresql function for checking date ranges

I'm not sure how to check for date ranges using a postgres function. What I want to do is check if a date falls within a certain range (with leeway of a week before the starting date)

So basically, I want to check if a date is between 7 days before to current date, and if so I'll return the id of that row.

create or replace function eight(_day date) returns text as $$
declare
  r record;
  check alias for $1;
  startDate date;
begin
    for r in
    select * from terms
    order by starting;
  loop
    startDate := r.starting;
    if check between (..need help to create 7 days before startDate) and startDate return r.id;


end;
$$ language plpgsql;

I also have to check if the previous record's ending date collides with the startDate - 7days. How would I check the previous record?

Upvotes: 0

Views: 1579

Answers (2)

Chris Travers
Chris Travers

Reputation: 26474

Dates work with integer math.

startdate - 8 is equivalent to (startdate::timestamp - '8 days'::interval)::date

Upvotes: 0

mu is too short
mu is too short

Reputation: 434975

Sounds like you want to use an interval:

startDate - interval '...'

I won't say any more than this since you're doing homework.

Upvotes: 1

Related Questions