Postgres生日选择 [英] Postgres birthdays selection

问题描述

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

I work with a Postgres database. This DB has a table with users, who have a birthdate (date field). Now I want to get all users who have their birthday in the upcoming week....

我的第一次尝试：从public.users中选择ID身分证（兰芝）和生日（> NOW（）AND birthdate< NOW（）+间隔 1周

但这不会导致结果，显然是因为不在一年中。我该如何解决这个问题？

But this does not result, obviously because off the year. How can I work around this problem?

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

And does anyone know what happen to PG would go with the cases at 29-02 birthday?

推荐答案

我们可以使用postgres函数以一种非常不错的方式执行此操作。

We can use a postgres function to do this in a really nice way.

假设我们有表 people ，并且在 dob 列中有出生日期，这是一个日期，我们可以创建一个函数这将使我们能够索引该列而忽略年份。 （感谢ZoltánBöszörményi）：

Assuming we have a table people, with a date of birth in the column dob, which is a date, we can create a function that will allow us to index this column ignoring the year. (Thanks to 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));

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

Now, we need to query against the table, and the index. For instance, to get everyone who has a birthday in April of any year:

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

有一个陷阱：如果我们的开始/结束时期超过了一年的界限，我们需要改变查询：

There is one gotcha: if our start/finish period crosses over a year boundary, we need to change the query:

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

要确保我们匹配leap日生日，我们需要知道是否会移动它们向前或向后的一天。就我而言，两天都比较简单，所以我的一般查询如下：

To make sure we match leap-day birthdays, we need to know if we will 'move' them a day forward or backwards. In my case, it was simpler to just match on both days, so my general query looks like:

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

我有一个django queryset方法，它使这一切变得更加简单：

I have a django queryset method that makes this all much simpler:

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)

现在，我可以做以下事情：

Now, I can do things like:

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

仅在前一天匹配o日生日，否则r也可能只在第二天出现：您只需要将SQL测试更改为> =或< =，而无需在python函数中调整开始/结束。

Matching leap-day birthdays only on the day before, or only the day after is also possible: you just need to change the SQL test to a `>=' or '<=', and not adjust the start/finish in the python function.

这篇关于Postgres生日选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！