У меня есть таблица базы данных под названием subscriptions
, Каждая строка в таблице имеет created_at
дата, начиная с этого дня, каждый месяц (после) подписка должна быть выставлена счет.
Скоро 2017-05-29
все подписки с created_at
день == «29» должен быть выставлен счет, независимо от месяца или года. Вот я и подумал об этом:
SELECT * FROM subscriptions WHERE DAY(created_at) = DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH))
Но в этом случае у меня возникают проблемы, когда в предыдущем месяце 30 дней, потому что 30-го числа он возвращает 30, но 31-го числа он также возвращает 30. Поэтому все подписки с created_at
день ’30’ будет выставлен счет дважды. Также февраль даст проблемы.
Другой вопрос — наоборот, как я буду выставлять счет на 2017-03-31, если в апреле нет 31-го дня.
Я мог бы сделать многократную проверку в PHP и проверить, выставлен ли счет уже в этом месяце и т. Д. Но мне интересно, смогу ли я просто исправить это с MySQL.
Я создал sqlfiddle Например, на основе запроса выше с некоторыми датами, которые потерпят неудачу.
# Create subscription table
CREATE TABLE `subscription` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`company` varchar(100) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# Fill subscription table
INSERT INTO `subscription` (`id`, `company`, `created_at`)
VALUES
(1, 'Acme', '2017-04-15 09:56:00'),
(2, 'Equmbo', '2017-02-28 10:00:00'),
(3, 'Megajo', '2017-03-31 08:10:34'),
(4, 'Astrotude', '2017-04-30 08:10:49');
# This is my base query for monthly invoice
SELECT * FROM subscription WHERE DAY(created_at) = DAY(DATE_SUB(CURDATE(), INTERVAL 1 MONTH));
# On 28th March, also fine
SELECT * FROM subscription WHERE DAY(created_at) = DAY(DATE_SUB('2017-03-28', INTERVAL 1 MONTH));
# But on 29th March, Equmbo is invoice again
SELECT * FROM subscription WHERE DAY(created_at) = DAY(DATE_SUB('2017-03-29', INTERVAL 1 MONTH));
# On 30st April, Astrotude AND Megajo must be invoiced. Only Astrotude returns.
SELECT * FROM subscription WHERE DAY(created_at) = DAY(DATE_SUB('2017-04-30', INTERVAL 1 MONTH));
WHERE
/* don't invoice new subs from this month */
LAST_DAY(created_at)<LAST_DAY(CURDATE())
AND
(
/* exactly match sub's day value with today's day value */
DAY(created_at)=DAY(CURDATE())
OR
/* on last day of month, match sub's day if greater than today's day */
(CURDATE()=LAST_DAY(CURDATE()) AND DAY(created_at)>DAY(CURDATE()))
)
CURDATE () а также ПОСЛЕДНИЙ ДЕНЬ() вернуть полную строку даты (гггг-мм-дд).
ДЕНЬ() возвращает целое число (d или dd).
Других решений пока нет …