Rebecca Clarke-2 wrote > 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. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/collecting-employees-who-completed-5-and-10-years-in-the-current-month-tp5809762p5809828.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
