From: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: collecting employees who completed 5 and 10 years in the current month |
Date: | 2014-06-30 17:08:46 |
Message-ID: | 1404148126521-5809828.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Kynn Jones | 2014-06-30 20:23:42 | how to create a role with no privileges? |
Previous Message | Szymon Guz | 2014-06-30 16:55:36 | Re: collecting employees who completed 5 and 10 years in the current month |