Меню
Что нового?

Задача о пересечении отрезков в датах

Temp1ar

Новичок
Задача о пересечении отрезков в датах

Задача следующая: есть график работы пользователя, он хранится в виде
Код: 
mysql> DESCRIBE schedules;
+------------+---------+------+-----+----------+----------------+
| Field      | Type    | Null | Key | Default  | Extra          |
+------------+---------+------+-----+----------+----------------+
| id         | int(11) | NO   | PRI | NULL     | auto_increment | 
| user_id    | int(11) | YES  | MUL | NULL     |                | 
| date       | date    | YES  | MUL | NULL     |                | 
| begin_time | time    | YES  |     | 00:00:00 |                | 
| end_time   | time    | YES  |     | 00:00:00 |                | 
+------------+---------+------+-----+----------+----------------+
За день можно много раз начинать и заканчивать работу:
Код: 
+----+---------+------------+------------+----------+
| id | user_id | date       | begin_time | end_time |
+----+---------+------------+------------+----------+
| 11 |       1 | 2008-08-14 | 13:00:00   | 15:00:00 | 
| 12 |       1 | 2008-08-14 | 16:00:00   | 17:00:00 | 
| 13 |       1 | 2008-08-14 | 18:00:00   | 18:08:00 | 
| 14 |       1 | 2008-08-14 | 18:08:00   | 18:11:00 | 
+----+---------+------------+------------+----------+
Нужно вычислить количество рабочих секунд при известной дате начала и дате конца, даты могут отличаться на день или несколько.
Тоесть, начальная дата: 2008-08-14 12:00, конечная 2008-08-14 16:30, вывод должен быть равен 2,5 часа
Есть ли какой-нибудь красивый способ решить подобную задачу, в IF'ах я закопался :(
Заранее спасибо!
 

Сергей123

Новичок
Хранить конец и начало в timestamp.
Делать SUM от их разницы, забивая в WHERE ограничение по датам.
 

Tigra666

Новичок
В чём проблема?


PHP: 
SELECT id,2.5*sum(time_to_sec(time_diff(end_time,begin_time))) as multi_seconds
FROM `schedules`
where date>'2008-01-01' and date<CURDATE()
group by id
А вообще,читаем мануалы
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_timediff

И какие ифы? Если вытащить всю таблицу в PHP то конечно можно закопаться... но глупо.
 

Temp1ar

Новичок
Автор оригинала: Tigra666
В чём проблема?


PHP: 
SELECT id,2.5*sum(time_to_sec(time_diff(end_time,begin_time))) as multi_seconds
FROM `schedules`
where date>'2008-01-01' and date<CURDATE()
group by id
А вообще,читаем мануалы
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_timediff

И какие ифы? Если вытащить всю таблицу в PHP то конечно можно закопаться... но глупо.
Нажмите для раскрытия...
А откуда в селекте 2.5*sum, это же то что надо найти.
Пошёл по ссылкам...

-~{}~ 23.08.08 16:07:

Автор оригинала: Бресь Сергей
Хранить конец и начало в timestamp.
Делать SUM от их разницы, забивая в WHERE ограничение по датам.
Нажмите для раскрытия...
timestamp неудобно, так как нужно будет строить отчёты, и получить его легко ADDTIME(date, begin_time)

-~{}~ 23.08.08 16:08:

Автор оригинала: Tigra666
http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#function_timediff

И какие ифы? Если вытащить всю таблицу в PHP то конечно можно закопаться... но глупо.
Нажмите для раскрытия...
Я про IF в запросе

-~{}~ 23.08.08 16:11:

TIMEDIFF я заменял простым переводом в секунды, для наглядности одна из версий моего запроса:
PHP: 
 SELECT  SUM( IF( (
(
TIME_TO_SEC( ADDTIME( `date` , `end_time` ) ) - TIME_TO_SEC( '2008-08-14 12:00' ) ) > TIME_TO_SEC( '2008-08-14 15:05' ) - TIME_TO_SEC( ADDTIME( `date` , `begin_time` ) )
AND TIME_TO_SEC( '2008-08-14 12:00' ) > TIME_TO_SEC( ADDTIME( `date` , `begin_time` ) ) ) , (
TIME_TO_SEC( ADDTIME( `date` , `end_time` ) ) - TIME_TO_SEC( '2008-08-14 12:00' )
), TIME_TO_SEC( '2008-08-14 15:05' ) - TIME_TO_SEC( ADDTIME( `date` , `begin_time` ) )
)
) AS in_work
FROM `schedules`
WHERE (
`schedules`.`user_id` =1
)
AND (
(
ADDTIME( `date` , `begin_time` ) <= '2008-08-14 12:00:00'
AND ADDTIME( `date` , `end_time` ) > '2008-08-14 12:00:00'
)
OR (
ADDTIME( `date` , `begin_time` ) < '2008-08-14 15:05:00'
AND ADDTIME( `date` , `end_time` ) >= '2008-08-14 15:05:00'
)
OR (
ADDTIME( `date` , `begin_time` ) >= '2008-08-14 12:00:00'
AND ADDTIME( `date` , `end_time` ) <= '2008-08-14 15:05:00'
)
)
GROUP BY user_id
Но он не охватывает все возможные случаи :(

-~{}~ 23.08.08 16:12:

Выдает 2:05, а должен 2:00 :(
 

Tigra666

Новичок
&#2222;Мда уж

Надо по конкретному юзеру так добавить туда же,в where.

&#2222;Что значит это то чно надо найти?
sum(time_to_sec(time_diff(end_time,begin_time))) это рабочее время в секундах для юзера,

И делай с этим что хочешь,даже можешь умножить на,как ты выразился 2,5 часа и получить новую единицу времени,секунды в квадрате.
Интеренсо где так зарплату выдают,,,Деньги наверно в корне будут.

И да,group by надо делать по user_id а не по id,что естествено,недосмотрел,
 

Сергей123

Новичок
>> timestamp неудобно, так как нужно будет строить отчёты, и получить его легко ADDTIME(date, begin_time)

конечно, по топику видно, что дата удобнее

P.S. А ещё бывают пользователи из разеых часовых поясов.
 

dadoc

Новичок
PHP: 
SELECT
SEC_TO_TIME(
    SUM(
        TIME_TO_SEC(
            IF('2008-08-14 16:30:00' < CONCAT(`date`,' ',end_time),
               '2008-08-14 16:30:00',
               CONCAT(`date`,' ',end_time)
            )
        )
        -
        TIME_TO_SEC(
            IF('2008-08-14 12:00:00' > CONCAT(`date`,' ',begin_time),
            '2008-08-14 12:00:00',
            CONCAT(`date`,' ',begin_time)
            )
        )
    )
)
FROM schedules
WHERE
CONCAT(`date`,' ',begin_time) BETWEEN '2008-08-14 12:00:00' AND '2008-08-14 16:30:00'
OR
CONCAT(`date`,' ',end_time) BETWEEN '2008-08-14 12:00:00' AND '2008-08-14 16:30:00'
GROUP BY user_id
Было бы удобнее хранить в datetime в таком случае.
 

Temp1ar

Новичок
PHP: 
SELECT
SEC_TO_TIME(
    SUM(
        TIME_TO_SEC(
            IF('2008-08-14 16:30:00' < CONCAT(`date`,' ',end_time),
               '2008-08-14 16:30:00',
               CONCAT(`date`,' ',end_time)
            )
        )
        -
        TIME_TO_SEC(
            IF('2008-08-14 12:00:00' > CONCAT(`date`,' ',begin_time),
            '2008-08-14 12:00:00',
            CONCAT(`date`,' ',begin_time)
            )
        )
    )
)
FROM schedules
WHERE
CONCAT(`date`,' ',begin_time) BETWEEN '2008-08-14 12:00:00' AND '2008-08-14 16:30:00'
OR
CONCAT(`date`,' ',end_time) BETWEEN '2008-08-14 12:00:00' AND '2008-08-14 16:30:00'
OR (
'2008-08-22 12:00:00' BETWEEN CONCAT(`date`,' ',begin_time) AND CONCAT(`date`,' ',end_time)
AND
'2008-08-22 16:30:00' BETWEEN CONCAT(`date`,' ',begin_time) AND CONCAT(`date`,' ',end_time)
)
GROUP BY user_id
Спасибо, большое, вариант подошёл, только я дописал условие, на случай если весь промежуток внутри одной записи
 
Войдите или зарегистрируйтесь для ответа.
Сверху