> create view vw_employee as > select * from employees > where ((age(joining_date::date) like '5 years%') or > (age(joining_date::date) like '10 years%') )
This does not give the correct answer to the poster's question - the LIKE with a trailing "%" will pick up non-round intervals.
> create view vw_employee as > select * from employees > where > ((to_char(joining_date, 'YYYY-MM') = to_char((now() - interval '5 > years'), 'YYYY-MM') ) > or > (to_char(joining_date, 'YYYY-MM') = to_char((now() - interval '10 > years'), 'YYYY-MM')))
This works - find out what year-month it was x years ago and compare it to the corresponding year-month of the requested date.
If one were to be doing this often it would probably be worth while to either use a functional index or a trigger-maintained field to store the "to_char(joining_date)" calculation.
WHERE joining_date_yearmonth = ANY( ARRAY['2009-06','1999-06']::text[] );
Was also pondering using a VARIADIC function to pass in integer year(s), which would then be converted into the corresponding array.
Haven't actually played with the above and so not sure how index-friendly the =ANY(...) construct is but it does allow you to avoid add entire OR clauses and instead simply supply a different comparison array.
