Postgres и дни рождения

Insolita
3 min readOct 9, 2021

Выборка дней рождений в базе данных — очень распространенная задача, но каждый раз вызывает трудности у новичков, поэтому решила накидать заметочку.
Важно: Подразумевается, что мы запрашиваем полную дату дня рождения и сохраняем в поле типа DATE

В Postgres для обработки “частей” даты есть функция date_part

Допустим у нас есть табличка типа

CREATE TABLE users (
id serial primary key,
name varchar(100) not null,
birthday date
);
  1. Выбираем именинников за текущий месяц
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 с упомянутыми запросами

--

--

Insolita

#php,#yii,#laravel,#javascript, #python,#linux,#archlinux,#vue