Postgres 生日选择

Question

我使用 Postgres 数据库。该数据库有一个包含用户的表，其中有出生日期（日期字段）。现在我想让所有在下周过生日的用户......

我的第一次尝试：SELECT id FROM public.users WHERE id IN (lange reeks) ANDbirthdate > NOW() 和出生日期 < NOW() + Interval '1 week'

但这并没有结果，显然是因为过年了。我该如何解决这个问题？

有谁知道 PG 在 29-02 生日时会发生什么情况？

Answer 1

我们可以使用 postgres 函数以一种非常好的方式来做到这一点。

假设我们有一个表 people，在 dob 列中包含出生日期，这是一个日期，我们可以创建一个函数，允许我们索引该列而忽略那一年。 （感谢Zoltán Böszörményi）：

CREATE OR REPLACE FUNCTION indexable_month_day(date) RETURNS TEXT as $BODY$
  SELECT to_char($1, 'MM-DD');
$BODY$ language 'sql' IMMUTABLE STRICT;

CREATE INDEX person_birthday_idx ON people (indexable_month_day(dob));

现在，我们需要查询针对表和索引。例如，要获取在任何一年的四月生日的每个人：

SELECT * FROM people 
WHERE 
    indexable_month_day(dob) >= '04-01'
AND 
    indexable_month_day(dob) < '05-01';

有一个问题：如果我们的开始/结束时间跨过一年边界，我们需要更改查询：

SELECT * FROM people 
WHERE 
    indexable_month_day(dob) >= '12-29'
OR 
    indexable_month_day(dob) < '01-04';

为了确保我们匹配闰日生日，我们需要知道我们是否会将它们向前或向后“移动”一天。就我而言，在这两天进行匹配更简单，因此我的一般查询如下所示：

SELECT * FROM people 
WHERE 
    indexable_month_day(dob) > '%(start)%'
%(AND|OR)%
    indexable_month_day(dob) < '%(finish)%';

我有一个 django 查询集方法，使这一切变得更加简单：

def birthday_between(self, start, finish):
    """Return the members of this queryset whose birthdays
    lie on or between start and finish."""
    start = start - datetime.timedelta(1)
    finish = finish + datetime.timedelta(1)
    return self.extra(where=["indexable_month_day(dob) < '%(finish)s' %(andor)s indexable_month_day(dob) > %(start)s" % {
        'start': start.strftime('%m-%d'),
        'finish': finish.strftime('%m-%d'),
        'andor': 'and if start.year == finish.year else 'or'
    }]

def birthday_on(self, date):
    return self.birthday_between(date, date)

现在，我可以执行以下操作：

Person.objects.birthday_on(datetime.date.today())

仅在前一天或仅后一天也是可能的：您只需将 SQL 测试更改为“>=”或“<=”，而不调整 python 函数中的开始/结束。

Answer 2

我对此并不太有信心，但它似乎在我的测试中起作用。这里的关键是 OVERLAPS 运算符和一些日期算术。

我假设你有一个表：

create temporary table birthdays (name varchar, bday date);

然后我在其中放入一些内容：

insert into birthdays (name, bday) values 
('Aug 24', '1981-08-24'), ('Aug 04', '1982-08-04'), ('Oct 10', '1980-10-10');

这个查询将为我提供下周生日的人：

select * from 
  (select *, bday + date_trunc('year', age(bday)) + interval '1 year' as anniversary from birthdays) bd 
where 
  (current_date, current_date + interval '1 week') overlaps (anniversary, anniversary)

date_trunc 截断当年的日期，所以它应该让你直到本年度。我最终不得不增加一年。这对我来说表明我出于某种原因有一个一对一的情况。也许我只需要找到一种方法来对日期进行四舍五入。无论如何，还有其他方法可以进行此计算。 age 为您提供从日期或时间戳到今天的间隔。我正在尝试添加生日和今天之间的年份以获得当年的日期。

真正的关键是使用重叠来查找日期重叠的记录。我使用周年纪念日两次来获取时间点。

Answer 3

最后，为了显示接下来 14 天即将到来的生日，我使用了以下内容：

SELECT 
    -- 14 days before birthday of 2000
    to_char( to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') - interval '14 days' , 'YYYY-MM-dd')  as _14b_b2000,
    -- birthday of 2000
    to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') as date_b2000,
    -- current date of 2000
    to_date(to_char(current_date, '2000-MM-dd'), 'YYYY-MM-dd') as date_c2000,
    -- 14 days after current date of 2000
    to_char( to_date(to_char(current_date, '2000-MM-dd'), 'YYYY-MM-dd') + interval '14 days' , 'YYYY-MM-dd') as _14a_c2000,
    -- 1 year after birthday of 2000
    to_char( to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') + interval '1 year' , 'YYYY-MM-dd') as _1ya_b2000
FROM c
WHERE 
    -- the condition 
    -- current date of 2000 between 14 days before birthday of 2000 and birthday of 2000
    to_date(to_char(current_date, '2000-MM-dd'), 'YYYY-MM-dd') between 
        to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') - interval '14 days' and 
        to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') 
    or 
    -- 1 year after birthday of 2000 between current date of 2000 and 14 days after current date of 2000
    to_date(to_char(c.birthdate, '2000-MM-dd'), 'YYYY-MM-dd') + interval '1 year' between 
        to_date(to_char(current_date, '2000-MM-dd'), 'YYYY-MM-dd') and 
        to_date(to_char(current_date, '2000-MM-dd'), 'YYYY-MM-dd') + interval '14 days' 
;

所以：
为了解决闰年问题，我将生日和当前日期都设置为2000年，
并仅处理从该初始正确日期开始的间隔。

为了处理临近结束/开始日期，
我首先将 2000 年当前日期与 2000 年生日间隔进行比较，
如果当前日期是年底，生日是年初，
我将 2001 年生日与 2000 年当前日期间隔进行了比较。

Answer 4

这是一个在大多数情况下都能得到正确结果的查询。

SELECT 
    (EXTRACT(MONTH FROM DATE '1980-08-05'),
     EXTRACT(DAY FROM DATE '1980-08-05')) 
IN (
    SELECT EXTRACT(MONTH FROM CURRENT_DATE + s.a) AS m,
           EXTRACT(DAY FROM CURRENT_DATE + s.a) AS d 
    FROM GENERATE_SERIES(0, 6) AS s(a)
);

（它不能正确处理闰年；但您可以再次使用 extract 来根据闰年而不是当前年份来进行子选择。

编辑：它适用于所有情况，并且作为一个有用的查询而不是标量选择，我使用了一些额外的子选择，这样我就不必为月份和日期输入相同的日期或表达式两次，当然还有实际的日期或表达式。数据将在表中而不是在values 表达式。您可能仍会通过为包含闰日的周制作更智能的系列来对此进行改进，因为有时该间隔仅包含 6 天（对于非闰年）。 我将尝试从内到外解释这一点；

我要做的第一件事是将目标日期（通常是 CURRENT_DATE，但在此代码中明确表示）标准化为我知道是闰年的年份。 ，因此 2 月 29 日出现在下一步是生成与所有正在考虑的月日对的关系；由于没有简单的方法来进行月日间隔检查，所以这一切都是使用 generate_series 进行的，

从那里，从目标关系中提取月份和日期是一个简单的问题（>people 别名）并仅过滤子选择中的行。

SELECT * 
FROM 
    (select column1 as birthdate, column2 as name
    from (values 
        (date '1982-08-05', 'Alice'),
        (date '1976-02-29', 'Bob'),
        (date '1980-06-10', 'Carol'),
        (date '1992-06-13', 'David')
    ) as birthdays) as people 
WHERE 
    ((EXTRACT(MONTH FROM people.birthdate), 
     EXTRACT(DAY FROM people.birthdate)) IN (
        SELECT EXTRACT(MONTH FROM thedate.theday + s.a) AS m,
               EXTRACT(DAY FROM thedate.theday + s.a) AS d
        FROM 
                (SELECT date (v.column1 - 
                        (extract (YEAR FROM v.column1)-2000) * INTERVAL '1 year'
                       ) as theday
                 FROM (VALUES (date '2011-06-09')) as v) as thedate,
                 GENERATE_SERIES(0, 6) AS s(a)
        )
    )

正如我在这里所做的那样，按天进行操作应该会一直很好地工作，直到两个月的间隔（如果您想观察那么远），从 12 月 31 日起+两个月，变化应该包括闰日。另一方面，对于这样的查询，只处理整个月几乎肯定更有用，在这种情况下，除了 extract(month from ....

Answer 5

首先使用 age() 找出此人当前的年龄，然后从 extract(year from Age()) 中获取年份。这是他们当前的年龄（以岁为单位），因此他们下一次生日时的年龄在年份上加 1。然后，通过在他们的生日上添加这么多年的间隔*间隔'1年'来找到他们的下一个生日。完毕。

我在这里使用了子选择将 next_birth_day 列添加到完整的表中，以使 select 子句更简单。然后，您可以使用 where 条件来满足您的需求。

select * 
from (
     select *, 
       (extract(year from age(birth_date)) + 1) *  interval '1 year' + birth_date "next_birth_day"
      from public.users
) as users_with_upcoming_birth_days
where next_birth_day between now() and now() + '7 days'

Answer 6

这是基于 Daniel Lyons 的周年纪念想法，通过计算下一个生日和今天之间的间隔，只需使用 +/- 日期算术：

SELECT
    today,
    birthday,
    CASE
        WHEN this_year_anniversary >= today
        THEN this_year_anniversary
        ELSE this_year_anniversary + '1 year'::interval
    END - today < '1 week'::interval AS is_upcoming
FROM
    (
        SELECT
            today,
            birthday,
            birthday + years AS this_year_anniversary
       FROM
            (
                SELECT
                    today,
                    birthday,
                    ((
                        extract(year FROM today) - extract(year from birthday)
                    ) || ' years')::interval AS years
                FROM
                    (VALUES ('2011-02-28'::date)) AS t1 (today),
                    (VALUES
                        ('1975-02-28'::date),
                        ('1975-03-06'::date),
                        ('1976-02-28'::date),
                        ('1976-02-29'::date),
                        ('1976-03-06'::date)
                    ) AS t2 (birthday)
            ) AS t
    ) AS t;

Answer 7

如果您希望它与闰年一起使用：

create or replace function birthdate(date)
  returns date
as $
  select (date_trunc('year', now()::date)
         + age($1, 'epoch'::date)
         - (extract(year from age($1, 'epoch'::date)) || ' years')::interval
         )::date;
$ language sql stable strict;

然后：

where birthdate(birthdate) between current_date
                            and current_date + interval '1 week'

另请参阅：

获取 PostgreSQL 中今天生日的所有条目

Answer 8

示例：生日介于 1 月 20 日和 2 月 10 日之间

SELECT * FROM users WHERE TO_CHAR(birthdate, '1800-MM-DD') BETWEEN '1800-01-20' AND '1800-02-10'

为什么是 1800？
无论是任何一年；

在我的注册表中，我可以告知出生日期（带年份）或仅告知生日（不带年份），在这种情况下，我保存为 1800，以便更轻松地处理日期

Answer 9

这是我的看法，也适用于闰年：

CREATE OR REPLACE FUNCTION days_until_birthday(
    p_date date
    ) RETURNS integer AS $
DECLARE
    v_now date;
    v_days integer;
    v_date_upcoming date;

    v_years integer;

BEGIN
    v_now = now()::date;
    IF (p_date IS NULL OR p_date > v_now) THEN
        RETURN NULL;
    END IF;

    v_years = date_part('year', v_now) - date_part('year', p_date);

    v_date_upcoming = p_date + v_years * interval '1 year';

    IF (v_date_upcoming < v_now) THEN
        v_date_upcoming = v_date_upcoming + interval '1 year';
    END IF;

    v_days = v_date_upcoming - v_now;
    RETURN v_days;
END
$ LANGUAGE plpgsql IMMUTABLE;

Answer 10

我知道这篇文章很旧，但我遇到了同样的问题，并提出了这个简单而优雅的解决方案：
对于在过去 20 天内过生日的人来说，使用age() 并计算圈年非常简单：

SELECT * FROM c 
WHERE date_trunc('year', age(birthdate)) != date_trunc('year', age(birthdate + interval '20 days'))

Answer 11

我只是根据原始出生日期创建了今年的日期。

( DATE_PART('month', birth_date) || '/' || DATE_PART('day', birth_date) || '/' || DATE_PART('year', now()))::date between :start_date and :end_date

我希望这有帮助。