Нахождение первого и последнего дней месяца

Задача
У вас есть дата, и необходимо узнать дату первого или последнего дня месяца, к которому она относится, или дату первого или последнего дня месяца n месяцев назад.

Решение
Выполните смещение даты.

Обсуждение
Порой требуется вычислить дату, не имеющую фиксированной связи с исходной датой. Например, при нахождении первого дня месяца величина смещения исходной даты зависит от дня месяца даты и от длины месяца.

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

mysql> SELECT d, DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY) AS '1st of month'
-> FROM date_val;

+--------------+--------------+
| d | 1st of month |
+--------------+--------------+
| 1864-02-28 | 1864-02-01 |
| 1900-01-15 | 1900-01-01 |
| 1987-03-05 | 1987-03-01 |
| 1999-12-31 | 1999-12-01 |
| 2000-06-04 | 2000-06-01 |
+--------------+---------------+

В общем случае, для того чтобы найти первый день месяца для любого месяца на n месяцев раньше указанной даты, вычислите первый день месяца, к которому относится дата, затем сдвиньте результат на n месяцев:

DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL n MONTH)

Например, чтобы найти первый день предыдущего и последующего месяцев по отношению к указанной дате, рассматриваем n как –1 и 1:

mysql> SELECT d,
-> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL -1 MONTH)
-> AS '1st of previous month',
-> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL 1 MONTH)
-> AS '1st of following month'
-> FROM date_val;

+--------------+--------------+---------------------------+
| d | 1st of previous month | 1st of following month |
+--------------+--------------+---------------------------+
| 1864-02-28 | 1864-01-01 | 1864-03-01 |
| 1900-01-15 | 1899-12-01 | 1900-02-01 |
| 1987-03-05 | 1987-02-01 | 1987-04-01 |
| 1999-12-31 | 1999-11-01 | 2000-01-01 |
| 2000-06-04 | 2000-05-01 | 2000-07-01 |
+--------------+---------------+--------------------------+
Найти последний день месяца по указанной исходной дате несколько сложнее, так как месяцы могут иметь различную длину.


Однако последний день месяца – это всегда день перед первым днем следующего месяца, который мы уже умеем находить. Тогда общая процедура вычисления последнего дня месяца, на n месяцев отстоящего от указанной даты, будет такой:

1. Найти первый день месяца.
2. Сдвинуть результат на n+1 месяцев.
3. Сдвинуть на день назад.

Выражение SQL, выполняющее подобную операцию, выглядит так:

DATE_SUB(
DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL n+1 MONTH),
INTERVAL 1 DAY)

Вычислим, например, последний день для предыдущего, текущего и последующего месяцев для указанной даты (n будет равно –1, 0 и 1):

mysql> SELECT d,
-> DATE_SUB(
-> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL 0 MONTH),
-> INTERVAL 1 DAY)
-> AS 'last, prev. month',
-> DATE_SUB(
-> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL 1 MONTH),
-> INTERVAL 1 DAY)
-> AS 'last, this month',
-> DATE_SUB(
-> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d)-1 DAY),INTERVAL 2 MONTH),
-> INTERVAL 1 DAY)
-> AS 'last, following month'
-> FROM date_val;

+--------------+--------------+---------------+------------------------+
| d | last, prev.


month | last, this month | last, following month |
+--------------+--------------+---------------+------------------------+
| 1864-02-28 | 1864-01-31 | 1864-02-29 | 1864-03-31 |
| 1900-01-15 | 1899-12-31 | 1900-01-31 | 1900-02-28 |
| 1987-03-05 | 1987-02-28 | 1987-03-31 | 1987-04-30 |
| 1999-12-31 | 1999-11-30 | 1999-12-31 | 2000-01-31 |
| 2000-06-04 | 2000-05-31 | 2000-06-30 | 2000-07-31 |
+--------------+--------------+---------------+-------------------------+

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

mysql> SELECT d,
-> DATE_SUB(d,INTERVAL DAYOFMONTH(d) DAY)
-> AS 'last of previous month'
-> FROM date_val;

+--------------+---------------+
| d | last of previous month |
+--------------+---------------+
| 1864-02-28 | 1864-01-31 |
| 1900-01-15 | 1899-12-31 |
| 1987-03-05 | 1987-02-28 |
| 1999-12-31 | 1999-11-30 |
| 2000-06-04 | 2000-05-31 |
+--------------+---------------+

Основная особенность общего выражения нахождения последнего дня месяца в том, что оно начинается с вычисления первого дня месяца заданной даты.


Это удобно, так как вы всегда можете сдвинуть начало месяца вперед или назад для получения первого числа другого месяца, сдвинув которое на день назад, вы получите последний день предыдущего месяца. Если же определять значения последних дней месяцев, находя последний день месяца для указанной даты, а затем сдвигая его, результат далеко не всегда будет корректным, так как не во всех месяцах одинаковое количество дней. Например, некорректно находить последний день месяца, вычисляя последний день предыдущего месяца и добавляя месяц:

mysql> SELECT d,
-> DATE_ADD(DATE_SUB(d,INTERVAL DAYOFMONTH(d) DAY),INTERVAL 1 MONTH)
-> AS 'last of month'
-> FROM date_val;

+--------------+---------------+
| d | last of month |
+--------------+---------------+
| 1864-02-28 | 1864-02-29 |
| 1900-01-15 | 1900-01-31 |
| 1987-03-05 | 1987-03-28 |
| 1999-12-31 | 1999-12-30 |
| 2000-06-04 | 2000-06-30 |
+--------------+---------------+

Этот способ не подходит, так как часть день-месяца результирующего значения даты может быть неправильной. Для строк 1987-03-05 и 1999-12-31 последний день месяца вычислен неправильно.


И так будет каждый раз, когда в месяце, предшествующем заданной дате, меньше дней, чем в нужном месяце.

Оцените статью: (0 голосов)
0 5 0

Статьи из раздела MySQL на эту тему:
Выбор записей по временным характеристикам
Вывод значений TIMESTAMP в удобном для чтения виде
Вычисление возраста
Вычисление длины месяца
Вычисление интервалов между значениями времени

Вернуться в раздел: MySQL / 5. Работа с датами и временем