0

I'm trying to get all birthdays for the following week (Mon - Sun) starting on Monday.

The query should be able to find birthdays for the following Mon - Sun regardless of which day the query runs. So for example, if the query was run on Tuesday, it would still be able to show all birthdays for the following Mon - Sun.

I've tried the following and it appears to work, but I'm not sure how to get it to always "start" on Monday:

SELECT * FROM pet
WHERE 
    birth_date BETWEEN CURRENT_DATE + INTERVAL '7 days' 
    AND CURRENT_DATE + INTERVAL '14 days';

Note that I derived this answer from some older posts (2011-2013) on SO. I'm running Postgres 9.5, so I'm not sure if there's a newer, more optimized way of doing this.

Thanks in advance!

Erik Philips
  • 53,428
  • 11
  • 128
  • 150
jdixon04
  • 1,435
  • 16
  • 28
  • This posts first answer shows you how to get next monday then add 7 to get that Sunday http://stackoverflow.com/questions/27989762/get-this-weeks-mondays-date-in-postgres – MarkD Mar 24 '17 at 16:36

2 Answers2

3

This will give you the Monday of the following week you are in.

Works were Monday is the start of the week. 

select date_trunc('week', current_date +7);
VynlJunkie
  • 1,953
  • 22
  • 26
0

regardless of the week day this gives you every day of the next week:

select  (date_trunc('week', now()+interval'1 week')::timestamp + (t.days::text||' days')::interval )::date
from generate_series (0,6) t(days)

to use this in a query:

select t.birthdays
from pet,
generate_series(date_trunc('week', birth_date  +interval'1 week') ,
             date_trunc('week', birth_date +interval'2 week'),'1 day' ) t(birthdays)
light souls
  • 698
  • 1
  • 8
  • 17