Postgres And Birthday Dates

Processing user`s dates of birthday is a often task, but sometimes newbies has difficult with it. So, I decide to write a few snippets.

For ex. we have a table


CREATE TABLE users (
id serial primary key,
name varchar(100) not null,
birthday date
);


1. Find users who have birthdays at this month


SELECT * FROM users WHERE date_part('month', birthday) = date_part('month', CURRENT_DATE)


2. Find users who have birthdays today


SELECT * FROM users
WHERE date_part('day', birthday) = date_part('day', CURRENT_DATE)
AND date_part('month', birthday) = date_part('month', CURRENT_DATE)


2.1 Find users who have birthdays tomorrow


SELECT * FROM users
WHERE
date_part('day', birthday) = date_part('day', CURRENT_DATE + INTERVAL '1day')
AND
date_part('month', birthday) = date_part('month', CURRENT_DATE + INTERVAL '1day')


3. Find users who have birthdays during some period, for. ex. in recent month


WITH lastDayOfMonth as (
SELECT (date_trunc('month', CURRENT_DATE) + interval '1 month' - interval '1 day')::date as ldm
)
SELECT *
FROM users, lastDayOfMonth
WHERE (
date_part('month', birthday) = date_part('month', CURRENT_DATE) AND
date_part('day', birthday) BETWEEN date_part('day', CURRENT_DATE) AND date_part('day', ldm)
)
OR
(
date_part('month', birthday) = date_part('month', CURRENT_DATE + interval '1 month')
AND date_part('day', birthday) BETWEEN 1 AND date_part('day', CURRENT_DATE)
);


But this method will be works only for month interval.
If we need birthdays in recent 10 days, or 45 days... for more flexible calculation will better to use a days number of year. But we need take care about valid handling between years - for ex. 20 days since 25 december - is an interval 25.12.2021 - 14.01.2022


WITH lastDayOfYear AS (
SELECT (date_trunc('year', CURRENT_DATE) + interval '1 year' - interval '1 day')::date AS ldy
)
SELECT *
FROM users, lastDayOfYear
WHERE
(
-- if year is same
date_part('year', CURRENT_DATE) = date_part('year', CURRENT_DATE + interval '20days')
AND date_part('doy', birthday)
BETWEEN date_part('doy', CURRENT_DATE) AND date_part('doy', CURRENT_DATE + interval '20days')
)
OR (
date_part('year', CURRENT_DATE) < date_part('year', CURRENT_DATE + interval '20days')
AND (
date_part('doy', birthday) BETWEEN date_part('doy', CURRENT_DATE) AND date_part('doy', ldy)
OR date_part('doy', birthday) < (20 - date_part('day', age(ldy, CURRENT_DATE)))
)
);


4. Find users with age in some interval, for example, users with age between 30 and 40 years old.

It is easy to solve on backend side, when you can prepare sql

php
<?php
$from = date('Y-m-d', strtotime('-30years'));
$to = date('Y-m-d', strtotime('-40years'));
$sql = "SELECT * FROM users WHERE birthday BETWEEN '$from' AND '$to'";


But sometimes direct query may be more useful - you can do it with postgres function age that calculates an interval between dates

SQL
SELECT * FROM users WHERE age(birthday) BETWEEN interval '30 years' AND interval '40 years';


You can play with these queries in Db-Fiddle

Comments

[Fuat Tosun]

Thanks for share!