Вычисление разницы в годах между датами в MySQL

Быстрый ответ

Чтобы найти разницу в годах между двумя датами, воспользуйтесь функцией DATEDIFF:

-- Вычисляем разницу, словно измеряем обхват бицепса, но только это касается дат 😄
SELECT YEAR(EndDate) – YEAR(StartDate) AS YearDifference
FROM YourTable

Для более точного учёта месяцев и дней можно скорректировать результат следующим образом:

-- Это уникальная формула для расчёта!
SELECT
  YEAR(EndDate) – YEAR(StartDate) – 
  (DATE_FORMAT(EndDate, '%m%d') < DATE_FORMAT(StartDate, '%m%d')) AS ExactYearDifference
FROM YourTable

Замените StartDate и EndDate датами начала и окончания, а YourTable — названием своей таблицы.

Учёт деталей: високосные года и дни рождения

Если вам требуется точный расчёт, как, например, в случаях с високосными годами или ожидаемыми днями рождения или юбилеями, детали играют решающую роль. Использование (DATE_FORMAT(EndDate, '%m%d') < DATE_FORMAT(StartDate, '%m%d')) корректирует разницу в годах на единицу, если день и месяц начальной даты в этом году ещё не наступили.

Зачем усложнять? Используйте встроенные функции

В MySQL доступна функция TIMESTAMPDIFF, которая значительно упрощает задачу:

-- Запустите TIMESTAMPDIFF — и оставьте ей всю рутинную работу
SELECT TIMESTAMPDIFF(YEAR, StartDate, EndDate) AS YearDifference
FROM YourTable

Ещё один вариант — применить FLOOR и DATEDIFF для расчёта количества полных лет:

-- Учтём разницу, используя FLOOR и DATEDIFF
SELECT FLOOR(DATEDIFF(EndDate, StartDate) / 365) AS YearDifference
FROM YourTable

Эти запросы можно протестировать на такой платформе как SQLFiddle или аналогичной для выполнения SQL-запросов.

Только округлённые и положительные результаты

Если вам необходимы только округлённые или только положительные значения, примените ABS и TO_DAYS:

-- Получим округлённый результат для обеих дат
SELECT
  ROUND(ABS(TO_DAYS(EndDate) – TO_DAYS(StartDate)) / 365) AS RoundedYearDifference
FROM YourTable

Для изучения большего количества функций обратитесь к официальной документации MySQL.

Визуализация

Продемонстрируем расчёт возраста в SQL, сравнив его с подсчётом годовых колец на дереве 🌳:

-- Каждый кольцо — это год, а деревья — подобны живым календарям!
SELECT YEAR(CURDATE()) – YEAR(BirthDate) 
       – (DATE_FORMAT(CURDATE(), '%m%d') < DATE_FORMAT(BirthDate, '%m%d')) 
       AS Age
FROM People

Этот запрос вычисляет каждое "кольцо" (год) между датой рождения и текущей датой. Пример:

🌱 'BirthDate' = 1985-03-24 (молодое деревце) -> 🌳 'CurrentDate' = 2023-03-24 (взрослое дерево) 🔍 📏 Возраст = 38 "колец" (лет)

Метафора с деревом становится более точной, ведь новое кольцо добавляется только после полного прохождения года.

Особенности и лучшие практики для обеспечения производительности

Обязательно учтите особые случаи:

• Даты, приходящиеся на один и тот же год.
• Когда даты рождения полностью совпадают.
• Использование DATEDIFF при расчёте с учётом високосного дня.

Чтобы повысить производительность, индексируйте столбцы с датами при работе с большими наборами данных. Эффективная индексация уменьшит время выполнения запросов путём избегания полного сканирования таблиц.

Проверьте и убедитесь в правильности своих расчётов

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

-- Золотое правило: всегда надевайте "шлем" во время тестирования!
SELECT
  CASE
    WHEN YEAR(EndDate) = YEAR(StartDate) THEN 'Тот же год'
    WHEN DATE_FORMAT(EndDate, '%m%d') = '0229' AND MONTH(StartDate) > 2 THEN 'Коррекция для високосного дня'
    ELSE 'Несколько лет'
  END AS CaseType,
  YourCalculation AS YearDifference
FROM YourTable

Этот пример можно рассматривать как ваш SQL-шлем, который обеспечивает безопасность, учитывая особые случаи при расчёте разницы в годах.

Полезные материалы

1. DATEDIFF (Transact-SQL) – SQL Server | Microsoft Learn — Официальная документация по функции DATEDIFF в SQL Server.
2. T-SQL – How to calculate age with years, months, and days – Stack Overflow — Обсуждение на Stack Overflow о вычислении возраста с примерами.
3. Date and Time Conversions Using SQL Server — Советы по работе с датами и временем в T-SQL.
4. Expert's Guide to SQL Server Function Performance — Рекомендации по оптимизации функций SQL Server, включая DATEDIFF.