Two ways come to mind for me. They're pretty much the same as Szymon's, just minus the sample table creation. I would suggest creating a view instead, so you can just select from it whenever you please.
create view vw_employee as
select * from employees
where ((age(joining_date::date) like '5 years%') or (age(joining_date::date) like '10 years%') )
I have employee table. Where I have a column joining_date. Now I am looking for a way to get all employee, who completed 5 years, 10 years current month. How to do so ? I am not able to figure this out.
Regards, Arup Rakshit
Hi,
take a look at this example:
I've created a sample table:
create table users(id serial, joining_date date);
and filled it with sample data:
insert into users(joining_date) select now() - (j::text || 'days' )::interval from generate_series(1,10000) j;
Then the query showing up all users who complete 5 and 10 years this month can look like:
with u as ( select id, date_trunc('month', age(now()::date, joining_date)) age from users ) select * from u where u.age in ('5 years', '10 years');
