sql - 如何仅从 postgresQL 中的日期获取日期/月份部分

Question

大家好我的 pg 管理数据库中有一个表。此表中有一个 employee 表。具有以下字段:-1)姓名2)出生日期

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

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

我想从表中选择所有记录 出生日期

lies between 28-Jan and 16-feb

Answer 1

试试这个:

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

您也可以尝试 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);

结合 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');