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
Top comments (1)
Thanks for share!