如何从postgres SQL中获取日期/月部分?

Hello All I have a table in my pg admin database.There is an employee table in this table.Having the field:- 1)name 2)date_of_birth

你好，我的pg管理数据库中有一个表。在这个表中有一个employee表。拥有字段:- 1)名称2)date_of_birth。

Now the scenario is that I want to know the birth day for current date and upcoming 20 days For example if current date is 28-Jan-2013 then

现在的情况是，我想知道当前日期和即将到来的20天的出生日期，如果当前日期是28- 1 -2013。

1)from_date=28-Jan-2013
2)to_date=16-feb-2013

I want to select all the records from the table for which the date_of_birth

我要从表中选择date_of_birth的所有记录。

lies between 28-Jan and 16-feb

3 个解决方案

#1

Try this:

试试这个:

SELECT *
FROM   bdaytable
WHERE  bdate >= '2013-01-28'::DATE
AND    bdate <= '2013-02-16'::DATE;

You may also try overlaps:

你也可以尝试重叠:

SELECT *
FROM bdaytable
WHERE (bdate, bdate) 
OVERLAPS ('2013-01-28'::DATE, '2013-02-16'::DATE);

with extract, month, day:

与提取、月日:

SELECT *
FROM   bdaytable
WHERE  Extract(month from bdate) >= Extract(month from '2013-01-28'::DATE)
AND    Extract(month from bdate) <= Extract(month from '2013-02-16'::DATE)
AND    Extract(day from bdate) >= Extract(day from '2013-01-28'::DATE)
AND    Extract(day from bdate) <= Extract(day from '2013-02-16'::DATE);

Incorporating Now() and interval to make the query dynamic with current date:

合并现在()和间隔，使查询动态与当前日期:

SELECT *
FROM   bdaytable
WHERE  Extract(month from bdate) >= Extract(month from Now())
AND    Extract(month from bdate) <= Extract(month from Now() + Interval '20 day')
AND    Extract(day from bdate) >= Extract(day from Now())
AND    Extract(day from bdate) <= Extract(day from Now() + Interval '20 day');

#2

select *
from employee
where 
    to_char(date_of_birth, 'MMDD') between 
    to_char(current_date, 'MMDD') and to_char(current_date + 20, 'MMDD')

#3

I think this should work. Use interval.

我认为这应该奏效。使用间隔。

SELECT * 
FROM Employee
WHERE Date_Of_Birth >= now() AND Date_Of_Birth <= now() + interval '20 day'

If you want to get any birth date between these days (and year doesn't matter), then that would be slightly different.

如果你想在这段时间内获得任何出生日期，那就有点不一样了。

EDIT

编辑

If year doesn't matter, while I'm sure there is a better way, this could work. Basically it's just converting all years to a common year (in this case 2000) -- you could do the same with your input parameter as well.

如果年份无关紧要，而我确信有更好的方法，这可能行得通。基本上，它只是将所有年份转换为普通年份(在本例中为2000)——您也可以使用输入参数进行相同的操作。

(Date_Of_Birth + (2000-EXTRACT(YEAR FROM Date_Of_Birth) || ' years')::interval)

Curious what others have used in the past as this is probably not the most efficient.

好奇别人过去用过什么，因为这可能不是最有效的。

Good luck.

祝你好运。

#1