подсчёт кол-ва дней

[berkut]

подсчёт кол-ва дней

есть странная таблица событий

CREATE TABLE events (
  id mediumint(9) unsigned NOT NULL auto_increment,
  date1 datetime NOT NULL default '0000-00-00 00:00:00',
  date2 datetime NOT NULL default '0000-00-00 00:00:00',
  is_period enum('0','1') NOT NULL default '0',
  PRIMARY KEY  (id)
) ENGINE=MyISAM

если is_period == "0", то date2 может отсутствовать и в этом случае, date1, date2 - это отдельные даты. если is_period == 1, то даты указывают на границы временного интервала: с date1 по date2. Как можно подсчитать количество (уникальных)дней, в которых происходят события? т.е.

id-------------date1-------------date2-------------is_period
1-------------2007-12-01-------------2007-12-03-------------1
2-------------2007-12-02-------------2007-12-04-------------1
3-------------2007-12-05-------------0000-00-00-------------0

здесь кол-во дней == 5

есть корявая идея использовать отдельную таблицу event_dates(event_id - date), в которой расписывать все периоды по дням. т.е. первый ряд из примера превратиться в
1---2007-12-01
1---2007-12-02
1---2007-12-03

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

 

[Андрейка]

.

 

[svetasmirnova]

[sql]select sum(if(is_period=0,1,DATEDIFF(date2,date1))) from events;[/sql]

 

[berkut]

svetasmirnova к сожалению это не то, что нужно

подсчитать количество (уникальных)дней

день в этом контексте - это дата, без временной части - sql DATE()

-~{}~ 21.12.07 03:47:

т.е. нужно считать уникальные даты, не учитывая повторные вхождения

 

[svetasmirnova]

berkut
То есть
id-------------date1-------------date2-------------is_period
1-------------2007-12-01-------------2007-12-03-------------1
2-------------2007-12-02-------------2007-12-04-------------1
3-------------2007-12-05-------------0000-00-00-------------0
5 дней, и
id-------------date1-------------date2-------------is_period
1-------------2007-12-01-------------2007-12-03-------------1
2-------------2007-12-01-------------2007-12-04-------------1
3-------------2007-12-05-------------0000-00-00-------------0
тоже 5 дней?

 

[berkut]

да.

 

[Gas]

[sql]
CREATE TABLE `dt` (
`dt1` date NOT NULL,
`dt2` date NOT NULL
);
INSERT INTO dt VALUES('2007-12-01','2007-12-03'),
('2007-12-02','2007-12-04'),
('2007-12-05','0000-00-00'),
('2007-12-05','2007-12-10'),
('2007-12-07','2007-12-08'),
('2007-12-07','2007-12-11'),
('2007-12-11','0000-00-00'),
('2007-12-10','2007-12-12');

SET @d1=0, @d2=0;
SELECT SUM(if(dt1=0 or dt2=0,0,if(dt1=dt2,1,datediff(dt2, dt1)+1))) from (
SELECT IF(UNIX_TIMESTAMP(dt1)<=@d2, if(UNIX_TIMESTAMP(dt2)>@d2,
DATE(DATE_ADD(FROM_UNIXTIME(@d2), INTERVAL 1 DAY)), 0), dt1) as dt1,
IF(UNIX_TIMESTAMP(dt2)>@d2, dt2, 0) as dt2 ,
@d1:=UNIX_TIMESTAMP(dt1), if(UNIX_TIMESTAMP(dt2)>@d2 or @d2=0,@d2:=UNIX_TIMESTAMP(dt2),'no')
FROM dt
ORDER BY dt1, DATEDIFF(dt2, dt1) DESC) as t;
[/sql]

по идее оно, результат - 12 дней.
Возможно можно сделать и проще, но придумалось только так

 

[berkut]

Gas жесть! но не работает.

2007-12-22         2007-12-24
2007-12-21         0000-00-00

должно быть 4, выдаёт 3

-~{}~ 21.12.07 20:26:

вообщем-то, то на единицу меньше, то как надо, по тестам.

 

[Gas]

А вот так?
[sql]
SET @d2=0;
SELECT SUM(if(dt1=0 or dt2=0,0,if(dt1=dt2,1,datediff(FROM_UNIXTIME(dt2), FROM_UNIXTIME(dt1))+1)))
from (
SELECT IF(dt1+0<=@d2+0, if(dt2+0>@d2+0, @d2+24*3600, 0), dt1) as dt1,
IF(dt2+0>@d2+0, dt2, 0) as dt2,
if(dt2+0>@d2+0 or @d2=0,@d2:=dt2,'no')
FROM (SELECT UNIX_TIMESTAMP(dt1) as dt1, UNIX_TIMESTAMP(if(dt2='0000-00-00', dt1, dt2)) as dt2
from dt
ORDER BY dt1, dt2 DESC) as t1) AS t2;
[/sql]

 

[berkut]

2007-12-20 -- 2007-12-24
2007-12-21 -- 0000-00-00
даёт 6 вместо 5

-~{}~ 21.12.07 20:44:

чорт. чё за Помощник санитара PHPCluba? откуда взялось

 

[Gas]

даёт 6 вместо 5

у меня показывает 5, а не 6

чорт. чё за Помощник санитара PHPCluba? откуда взялось

видать после 500 постов

-~{}~ 21.12.07 19:03:

Хм, на 5.0.37 - результат 5, на 4.1.21 - 6
странно

 

[berkut]

2007-12-20 -- 2007-12-24
2007-12-21 -- 0000-00-00

у меня упорно выдаёт 6. уже и таблицу пересоздал. копи-паст с форума. может как-то связано с махинациями
UNIX_TIMESTAMP( DATE_ADD( FROM_UNIXTIME(

-~{}~ 21.12.07 21:11:

у меня и на 5.0.6-beta-nt-max и на 4.1.8-max 6 даёт.

 

[Gas]

Подправил запрос, там какие-то проблемы с преобразованием типов при сравнении значения поля и переменной.

Щас вроде нормально.

 

[berkut]

теперь на этих датах нормально. завтра потестирую и буду разбираться в этом монстре

 

[Gas]

ага, потесть если не сложно, может самому пригодится если это рабочий вариант.
Там смысл: сортируется массив по начальным датам и сравнивается поочерёдно записи друг с другом для поиска пересечений.