SQL 如何从postgres SQL中的日期中仅获取日期/月份部分
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14590508/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to get only date/month part from a date in postgres SQL
提问by Suri
Hello AllI have a table in my pg admin database.There is an employeetable in this table.Having the field:- 1)name 2)date_of_birth
大家好，我的 pg admin 数据库中有一个表。该表中有一个员工表。有字段：- 1) 姓名 2) 出生日期
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-Jan-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
回答by bonCodigo
Try this:
尝试这个：
SELECT *
FROM bdaytable
WHERE bdate >= '2013-01-28'::DATE
AND bdate <= '2013-02-16'::DATE;
You may also try overlaps:
您也可以尝试overlaps：
SELECT *
FROM bdaytable
WHERE (bdate, bdate)
OVERLAPS ('2013-01-28'::DATE, '2013-02-16'::DATE);
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 intervalto make the query dynamic with current date:
合并Now()并interval使用当前日期使查询动态化：
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');
回答by Clodoaldo Neto
select *
from employee
where
to_char(date_of_birth, 'MMDD') between
to_char(current_date, 'MMDD') and to_char(current_date + 20, 'MMDD')
回答by sgeddes
I think this should work. Use interval.
我认为这应该有效。使用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.
祝你好运。
