#mysql
#mysql
Вопрос:
У меня есть 2 дня, такие как check_in (25/04/2011) и check_out (06.04.2011). Теперь мне нужно рассчитать дни между датами в месячном исчислении.
ie. Месяц — Дни,
April - 5,
May - 31,
June - 4,
Пожалуйста, помогите мне в создании запроса mysql для получения вышеуказанного результата.
Заранее спасибо.
Комментарии:
1. Вам было бы лучше вычислить это на стороне клиента. В MySQL есть несколько замечательных функций даты, но вы бы излишне усложнили свой запрос
2. Проблема в том, что мне нужен запрос для создания отчета. Иначе мне, возможно, придется написать логику программы.
3. Как бы вы использовали выходные данные? Будут ли эти строки новыми выборками или они должны быть в 1 столбце?
4. Это похоже на отчет. т.е. сколько бронирований для конкретного отеля (т.Е. количество ночей в зависимости от дат check_in и выезда) имеется в каждом месяце. например. отель-ABChotel ЯНВАРЬ-10 ФЕВРАЛЬ-132……. ДЕКАБРЬ-100
Ответ №1:
Если вы хотите строго выполнять в MySQL, вам нужно создать хранимую процедуру для этого.
Что-то вроде этого в строке хранимой процедуры (и диапазон не выше года).
DROP PROCEDURE IF EXISTS `getDateDiffBreakdown`;
CREATE PROCEDURE `getDateDiffBreakdown`(_DATE1 DATE,_DATE2 DATE)
BEGIN
IF (MONTH(_DATE1)<>MONTH(_DATE2)) THEN
-- we detected a month change
-- compute the selection based on current date and last day of month
SELECT CONCAT(DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(LAST_DAY(_DATE1))-TO_DAYS(_DATE1) 1);
-- step into next month and re-run the calc
call getDateDiffBreakdown(DATE_ADD(LAST_DAY(_DATE1),INTERVAL 1 DAY),_DATE2);
ELSE
-- same month, do the calculation
SELECT CONCAT(DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(_DATE2)-TO_DAYS(_DATE1) 1);
END IF;
END;
вызовите следующим образом:
set max_sp_recursion_depth = 11;
call getDateDiffBreakdown('2011-12-11','2012-06-03');
Обновить
При другом подходе для получения в 1 строку, это было бы:
DROP PROCEDURE IF EXISTS `getDateDiffBreakdown2`;
CREATE PROCEDURE `getDateDiffBreakdown2`(IN _DATE1 DATE,IN _DATE2 DATE, INOUT _RETURN VARCHAR(1000))
BEGIN
IF (MONTH(_DATE1)<>MONTH(_DATE2)) THEN
-- we detected a month change
-- compute the selection based on current date and last day of month
SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(LAST_DAY(_DATE1))-TO_DAYS(_DATE1) 1);
SET _RETURN = CONCAT(_RETURN,",");
-- step into next month and re-run the calc
call getDateDiffBreakdown2(DATE_ADD(LAST_DAY(_DATE1),INTERVAL 1 DAY),_DATE2,_RETURN);
ELSE
-- same month, do the calculation
SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(_DATE2)-TO_DAYS(_DATE1) 1);
END IF;
END;
вызовите следующим образом:
set max_sp_recursion_depth = 255;
set @TEMP = '';
call getDateDiffBreakdown2('2011-12-11','2012-06-03',@TEMP);
SELECT @TEMP;
Комментарии:
1. 1 Протестировал ваше решение прямо сейчас. Ты всегда очень крут. 🙂
2. Спасибо, я бы позволил OP или кому-то еще настроить код для работы с диапазонами, охватывающими несколько месяцев разных лет.
Ответ №2:
Я тоже пытался решить проблему. Pentium10 слишком силен, и теперь я попробую его решение. 🙂 Кстати, это мое.
delimiter //
drop procedure if exists groupDaysByMonth//
create procedure groupDaysByMonth(in dStart date,in dEnd date)
begin
declare i int default 0;
declare months,days int;
drop table if exists t;
create temporary table t (
month_year varchar(50),
daysNum int
);
set months = (select period_diff(date_format(dEnd,'%Y%m'),date_format(dStart,'%Y%m')));
while i<=months do
if months = 0 then
set days = (select datediff(dEnd,dStart));
elseif i = 0 then
set days = ( select datediff(concat(date_format(dStart,'%Y-%m-'),day(last_day(dStart))),dStart));
elseif months = i then
set days = (select datediff(dEnd,date_format(dEnd,'%Y-%m-01')) 1);
else
set days = ( select day(last_day(dStart interval i month)));
end if;
insert into t (month_year,daysNum) values(date_format(dStart interval i month,'%M %Y'),days);
set i = i 1;
end while;
select * from t;
end //
delimiter ;
mysql> call groupDaysByMonth('2011-04-25','2011-04-30');
------------ ---------
| month_year | daysNum |
------------ ---------
| April 2011 | 5 |
------------ ---------
1 row in set (0.01 sec)
mysql> call groupDaysByMonth('2011-04-25','2011-06-04');
------------ ---------
| month_year | daysNum |
------------ ---------
| April 2011 | 5 |
| May 2011 | 31 |
| June 2011 | 4 |
------------ ---------
3 rows in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
mysql> call groupDaysByMonth('2011-09-25','2012-05-02');
---------------- ---------
| month_year | daysNum |
---------------- ---------
| September 2011 | 5 |
| October 2011 | 31 |
| November 2011 | 30 |
| December 2011 | 31 |
| January 2012 | 31 |
| February 2012 | 29 |
| March 2012 | 31 |
| April 2012 | 30 |
| May 2012 | 2 |
---------------- ---------
9 rows in set (0.01 sec)
Query OK, 0 rows affected (0.03 sec)
Надеюсь, что это поможет.
Ответ №3:
Follow Answer 1 -- i am using it for multi year n the result set is month number with year and then the days of month format is "month number - last two digit of year - total days in month."
you can change the format of display according your need
**UPDATE**
In another approach to get in 1 line, it would be:
DROP PROCEDURE IF EXISTS `getDateDiffBreakdown2`;
CREATE PROCEDURE `getDateDiffBreakdown2`(IN _DATE1 DATE,IN _DATE2 DATE, INOUT _RETURN VARCHAR(1000))
BEGIN
IF (MONTH(_DATE1)<>MONTH(_DATE2)) THEN
-- we detected a month change
-- compute the selection based on current date and last day of month
SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(LAST_DAY(_DATE1))-TO_DAYS(_DATE1) 1);
SET _RETURN = CONCAT(_RETURN,",");
-- step into next month and re-run the calc
call getDateDiffBreakdown2(DATE_ADD(LAST_DAY(_DATE1),INTERVAL 1 DAY),_DATE2,_RETURN);
ELSE
-- same month, do the calculation
SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(_DATE2)-TO_DAYS(_DATE1) 1);
END IF;
END;
call like this:
set max_sp_recursion_depth = 255;
set @TEMP = '';
call getDateDiffBreakdown2('2011-12-11','2012-06-03',@TEMP);
SELECT @TEMP;
I have applied this post in one of my requirement but i found it buggy. i can be wrong if any one prove it and provide a better solution.
look how i am calling it and what i am getting :
set max_sp_recursion_depth = 255;
set @TEMP = '';
call getDateDiffBreakdown2('2010-12-10' , '2011-12-10',@TEMP);
SELECT @TEMP;
in result i get : '12 - 10 - 366'
year is changed but month is same.
i have tweaked the function as following: kindly let me know if some thing is strange. thanks
DROP PROCEDURE IF EXISTS `getDateDiffBreakdown2`;
CREATE PROCEDURE `getDateDiffBreakdown2`(IN _DATE1 DATE,IN _DATE2 DATE, INOUT _RETURN VARCHAR(1000))
BEGIN
IF(YEAR(_DATE1)<>YEAR(_DATE2)) THEN
SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%b - %y'),' - ',TO_DAYS(LAST_DAY(_DATE1))-TO_DAYS(_DATE1) 1);
SET _RETURN = CONCAT(_RETURN,",");
-- step into next month and re-run the calc
call getDateDiffBreakdown2(DATE_ADD(LAST_DAY(_DATE1),INTERVAL 1 DAY),_DATE2,_RETURN);
ELSEIF (MONTH(_DATE1)<>MONTH(_DATE2)) THEN
-- we detected a month change
-- compute the selection based on current date and last day of month
SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(LAST_DAY(_DATE1))-TO_DAYS(_DATE1) 1);
SET _RETURN = CONCAT(_RETURN,",");
-- step into next month and re-run the calc
call getDateDiffBreakdown2(DATE_ADD(LAST_DAY(_DATE1),INTERVAL 1 DAY),_DATE2,_RETURN);
ELSE
-- same month, do the calculation
SET _RETURN=CONCAT(_RETURN,DATE_FORMAT(_DATE1,'%M'),' - ',TO_DAYS(_DATE2)-TO_DAYS(_DATE1) 1);
END IF;
END;
Ответ №4:
Используйте для этого функцию TO_DAYS (дата).
Комментарии:
1. Дело в том, что мне нужно вычислить разницу между check_in (25/04/2011) и check_out (06.04.2011). Использование datediff() Я получаю ответ как 40 дней. Но я хочу конкретно, как я предложил выше. ie — апрель — 5, май — 31, июнь — 4,