select birth_date,cast(birth_date + ((extract(year from age(birth_date)) + 1) * interval '1' year) as date) as next_birthday
from person
where name = 'Bob'
表达式(提取物(年龄(birth_date))1)*间隔“1”年计算(完整)年份下一个生日的年龄.将其添加到出生日期时,这将给下一个生日.
由于日期间隔返回时间戳(包括时间),因此需要使用强制转换才能获得真实的日期.
如果你删除where条件,你将获得所有“下一个”生日.
您还可以获取即将到来的生日列表,例如:接下来的30天使用这样的东西:
select next_birthday,next_birthday - current_date as days_until_next
from (
select birth_date,cast(birth_date + ((extract(year from age(birth_date)) + 1) * interval '1' year) as date) as next_birthday
from person
) as upcoming
where upcoming.next_birthday <= current_date + 30
order by next_birthday;