Выборка дней рождений в базе данных — очень распространенная задача, но каждый раз вызывает трудности у новичков, поэтому решила накидать заметочку.
Важно: Подразумевается, что мы запрашиваем полную дату дня рождения и сохраняем в поле типа DATE
В Postgres для обработки “частей” даты есть функция date_part
Допустим у нас есть табличка типа
CREATE TABLE users (
id serial primary key,
name varchar(100) not null,
birthday date
);
- Выбираем именинников за текущий месяц
SELECT * FROM users WHERE date_part('month', birthday) = date_part('month', CURRENT_DATE)
2. Выбираем именинников за текущий день:
SELECT * FROM users WHERE date_part('day', birthday) = date_part('day', CURRENT_DATE) AND date_part('month', birthday) = date_part('month', CURRENT_DATE)
Если нужен не текущий. а завтрашний день — например для предварительной рассылки
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. Выбираем родившихся в заданный промежуток (например в течение месяца с текущей даты)
И на этой задаче очень часто встречаются некорректные решения, потому что важно учитывать переходный промежуток между концом года и началом следующего
Вариант 1 — с помощью генерации запроса может выглядеть так
<?php
$daysInMonths=[
'01' =>31,
'02'=>date('Y')%4 ===0?29:28,
'03' =>31
...
'12' =>31];$currentMonth = date('m');$nextMonth = date('m', strtotime('+1month'));$currentDay = date('d');
$sql = "SELECT * FROM users
WHERE
(date_part('month', birthday) = '$currentMonth' AND date_part('day', birthday) BETWEEN '$currentDay' AND '".$dayInMonths[$currentMonth]."' )
OR
(date_part('month', birthday) = '$nextMonth'
AND date_part('day', birthday) BETWEEN 1 AND '$currentDay')
";
Вариант 2 — можно без генерации
Получить дату последнего дня месяца мы можем с помощью запроса
SELECT (date_trunc('month', CURRENT_DATE) + interval '1 month' - interval '1 day')::date;
Тогда наш запрос будет выглядеть так:
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)
);
Но так будет работать только имено для месячного диапазона, если нам нужен диапазон всего в 10 дней, 24 или 55, будет удобнее рассчитывать исходя из порядкового номера дня в году, а не конкретных дня и месяца
WITH lastDayOfYear AS (
SELECT (date_trunc('year', CURRENT_DATE) + interval '1 year' - interval '1 day')::date AS ldy
)
SELECT *
FROM users, lastDayOfYear
WHERE
(
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. Выборка по возрасту
Ещё один распространенный кейс — найти людей в возрастном диапазоне — например с 30 до 40 лет.
Это легко можно решить на программной стороне, подобрав нужные диапазоны дат
<?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'"
Но по каким-то причинам это может быть не удобно, или например нам нужно сделать выборку из базы напрямую — для этого в Postgres есть функция age которая возвращает интервал между датами SELECT age('2021-01-01')
вернёт нам интервал с начала 2021 года до текущей даты SELECT age('2021-02-05', '2021-01-01')
вернёт интервал между датами
Соответсвенно найти пользователей с возрастом от 30 до 40 лет мы можем запросом
SELECT * FROM users WHERE age(birthday) BETWEEN interval '30 years' AND interval '40 years';
Ну и на закуску db-fiddle с упомянутыми запросами