From: | Szymon Guz <mabewlun(at)gmail(dot)com> |
---|---|
To: | Arup Rakshit <aruprakshit(at)rocketmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: collecting employees who completed 5 and 10 years in the current month |
Date: | 2014-06-30 11:00:22 |
Message-ID: | CAFjNrYvHUwXJz_d9hWmcjEy5U=geTmTdxck1sVqdweJdf-jmaA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 30 June 2014 12:38, Arup Rakshit <aruprakshit(at)rocketmail(dot)com> wrote:
> 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');
- Szymon
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2014-06-30 11:34:37 | Re: [ADMIN] [pgadmin-support] Best backup strategy for production systems |
Previous Message | Arup Rakshit | 2014-06-30 10:38:53 | collecting employees who completed 5 and 10 years in the current month |