Postgresql function for checking date ranges

Question

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?

possible duplicate of PostgreSQL checking a previous record's element — Erwin Brandstetter
– Erwin Brandstetter, Commented Apr 21, 2012 at 14:07

mu is too short · Accepted Answer · 2012-04-21 05:42:13Z

1

Sounds like you want to use an interval:

startDate - interval '...'

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

answered Apr 21, 2012 at 5:42

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

4 Comments

SNpn Over a year ago

i was using if check between startDate - interval '7 days' but I'm not sure if that works

mu is too short Over a year ago

@SNpn: Why don't you think between startDate - interval '7 days' and startDate works?

SNpn Over a year ago

i wasn't sure if the '7 days' part would work, also could you read my edit in OP - bit stuck on how to tackle that.

Chris Travers Over a year ago

Added my answer. Just do between (startdate - 7) and startdate or the like. Dates work with integer math, where integers represent days.

Chris Travers · Accepted Answer · 2012-10-15 02:34:34Z

0

Dates work with integer math.

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

answered Oct 15, 2012 at 2:34

Chris Travers

26.6k6 gold badges70 silver badges191 bronze badges

Collectives™ on Stack Overflow

Postgresql function for checking date ranges

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related