-1

My data looks like below, and I need to show the ids where interval between date1 and date2 are part of a given month/year parameter. Eg.: for July 2018 I need ids from 1 to 7.

date1       date2           id  
----------  ----------  --------
2017-11-01  2018-08-28         1
2018-06-05  2018-07-05         2
2018-06-05  2019-05-07         3
2018-06-05  2018-08-08         4
2018-07-01  2018-07-31         5
2018-07-07  2018-07-15         6
2018-07-27  2018-08-05         7
2018-06-01  2018-06-07         8
2018-08-03  2018-09-01         9
Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
erickdeoliveiraleal
  • 708
  • 2
  • 9
  • 21
  • 1
    It's usually helpful to you if you search for existing posts that can help you make an effort before you ask. I found https://stackoverflow.com/q/48928585/62576 from looking at the list of Related questions over there --->>> without even having to search. – Ken White Jul 12 '18 at 02:05
  • No. The question is different. And was well answered below. – erickdeoliveiraleal Aug 03 '18 at 01:28

1 Answers1

1

solution is quite simple

SELECT
id
FROM
YOUR_TABLE
WHERE
date1<=YOUR_DATE_END_OF_MONTH AND date2>=YOUR_DATE_START_OF_MONTH

e.g. for July 2018 

SELECT
id
FROM
YOUR_TABLE
WHERE
date1<='2018-07-31' AND date2>='2018-07-01'

or if you do not need to calculate first end day of the month (but this do not use any indexes if exists on date1 and date2)

SELECT
id
FROM
YOUR_TABLE
WHERE
EXTRACT(YEAR FROM date1)*12 + EXTRACT(MONTH FROM date1)<=2018*12 + 7 
AND EXTRACT(YEAR FROM date2)*12 + EXTRACT(MONTH FROM date2)>=2018*12 + 7
Livius
  • 958
  • 1
  • 6
  • 19