MySQL - 更改一行的时间值以匹配同一表的另一行的时间值

I have several consultants who record their job start/end times. Because they have two-hour minimum fees, I need to make sure that I properly calculate their job times. The problem arises when they have consecutive jobs on the same site, with idle times ranging between 15 to 60 minutes between jobs.

我有几位顾问记录他们的工作开始/结束时间。因为他们有两小时的最低费用,我需要确保我正确计算他们的工作时间。当他们在同一站点上连续工作时,问题就出现了,工作之间的空闲时间在15到60分钟之间。

The following is a query result of my sample table ordered by cid, job_date, start_time. For this particular example, all job time entries are for the same day:

以下是按cid,job_date,start_time排序的示例表的查询结果。对于此特定示例,所有作业时间条目都在同一天:

 id  job_num    cid job_date    start_time           end_time
 19  130513-20  6   05/13/2013  2013-05-13 13:30:00  2013-05-13 16:30:00
 10  130513-10  7   05/13/2013  2013-05-13 11:00:00  2013-05-13 13:00:00
 16  130513-17  13  05/13/2013  2013-05-13 14:45:00  2013-05-13 15:30:00
 15  130513-16  16  05/13/2013  2013-05-13 14:45:00  2013-05-13 16:15:00
 18  130513-19  34  05/13/2013  2013-05-13 11:15:00  2013-05-13 12:15:00
 8   130513-08  44  05/13/2013  2013-05-13 12:00:00  2013-05-13 14:30:00
 6   130513-06  47  05/13/2013  2013-05-13 11:00:00  2013-05-13 12:00:00
 9   130513-09  47  05/13/2013  2013-05-13 12:00:00  2013-05-13 12:30:00
 14  130513-15  47  05/13/2013  2013-05-13 14:30:00  2013-05-13 15:15:00
 3   130513-03  50  05/13/2013  2013-05-13 09:00:00  2013-05-13 10:45:00
 7   130513-07  50  05/13/2013  2013-05-13 11:00:00  2013-05-13 12:00:00
 11  130513-11  50  05/13/2013  2013-05-13 13:00:00  2013-05-13 14:30:00
 17  130513-18  50  05/13/2013  2013-05-13 15:00:00  2013-05-13 16:15:00
 2   130513-02  52  05/13/2013  2013-05-13 09:30:00  2013-05-13 10:30:00
 4   130513-04  52  05/13/2013  2013-05-13 10:30:00  2013-05-13 11:00:00
 5   130513-05  52  05/13/2013  2013-05-13 11:00:00  2013-05-13 13:00:00
 12  130513-12  52  05/13/2013  2013-05-13 14:00:00  2013-05-13 14:45:00
 13  130513-13  52  05/13/2013  2013-05-13 15:00:00  2013-05-13 17:00:00
 20  130513-21  61  05/13/2013  2013-05-13 11:00:00  2013-05-13 12:00:00
 1   130513-01  70  05/13/2013  2013-05-13 14:30:00  2013-05-13 15:15:00

What I am trying to accomplish is to update the end_time of a particular row (job_num) so that it is equal to the end_time of the next job_num in the query for the same consultant (cid), but only if the difference (idle time) between the end_time of the first job and the start_time of the following job <= 30 minutes. Basically, I want to add the idle time to the end_time of the first job in the comparison.

我想要完成的是更新特定行(job_num)的end_time,使其等于查询中同一顾问(cid)的下一个job_num的end_time,但仅限于差异(空闲时间)在第一个作业的end_time和后续作业的start_time之间<= 30分钟。基本上,我想在比较中将空闲时间添加到第一个作业的end_time。

For example:

 id  job_num    cid job_date    start                end                
 3   130513-03  50  05/13/2013  2013-05-13 09:00:00  2013-05-13 10:45:00  <-- original
 7   130513-07  50  05/13/2013  2013-05-13 11:00:00  2013-05-13 12:00:00 
 11  130513-11  50  05/13/2013  2013-05-13 13:00:00  2013-05-13 14:30:00  <-- original
 17  130513-18  50  05/13/2013  2013-05-13 15:00:00  2013-05-13 16:15:00 
...
 12  130513-12  52  05/13/2013  2013-05-13 14:00:00  2013-05-13 14:45:00  <-- original
 13  130513-13  52  05/13/2013  2013-05-13 15:00:00  2013-05-13 17:00:00 

become:

 3   130513-03  50  05/13/2013  2013-05-13 09:00:00  2013-05-13 11:00:00  <-- changed
 7   130513-07  50  05/13/2013  2013-05-13 11:00:00  2013-05-13 12:00:00 
 11  130513-11  50  05/13/2013  2013-05-13 13:00:00  2013-05-13 15:00:00  <-- changed
 17  130513-18  50  05/13/2013  2013-05-13 15:00:00  2013-05-13 16:15:00 
...
 12  130513-12  52  05/13/2013  2013-05-13 14:00:00  2013-05-13 15:00:00  <-- changed
 13  130513-13  52  05/13/2013  2013-05-13 15:00:00  2013-05-13 17:00:00 

How would I go about making this change? I have seen solutions to similar questions using temporary tables and variables that are very well suited to check for time differences but none that could be adapted (in my limited knowledge of MySQL queries) to my particular problem. I have also tagged this question as PHP because perhaps it would be simpler to do this in PHP.

我将如何进行此更改?我已经看到了使用临时表和变量的类似问题的解决方案,这些表和变量非常适合检查时间差异,但是没有一个可以根据我的特定问题进行调整(在我对MySQL查询的有限知识中)。我还将这个问题标记为PHP,因为在PHP中执行此操作可能会更简单。

Any help will be greatly appreciated!

任何帮助将不胜感激!

2 个解决方案

#1

I am assuming that you are formatting the start time and end time using php and then inserting into database table.

我假设您使用php格式化开始时间和结束时间,然后插入数据库表。

$start = date("Y-m-d H:i:s",time());
$query = "select * from table name where cid = $cid and  UNIX_TIMESTAMP($start) - UNIX_TIMESTAMP(end_time) <= 1800";
mysqli_query($con,$query);

if ($result=mysqli_query($con,$query))
  {
    $rowcount=mysqli_num_rows($result);

    if($rowcount > 1)
    { 
     $row = mysqli_fetch_array($result, MYSQLI_ASSOC);

     query2 = "update tablename set end_time = '$start' where id = '$row['id']'";

     query3 = "insert into tablename(cid,date,start_time,end_time) values($cid,$date,$start,$end)";


    }else{

    //do the insert as such
    query2 = "insert into tablename(cid,date,start_time,end_time) values($cid,$date,$start,$end)";

    }

    mysqli_free_result($result);
  }

#2

I assume that the id is the Primary Key of the table and it is AUTO-INCREMENTing.

我假设id是表的主键,它是AUTO-INCREMENTing。

I have this UPDATE query:

我有这个UPDATE查询:

UPDATE table T1
    SET T1.end_time = (
            SELECT T2.end_time
                FROM table T2
                WHERE T2.cid = T1.cid
                    AND T2.job_date = T1.job_date
                    AND T2.id > T1.id
                    AND T2.start_time <= DATE_ADD(T1.end_time, INTERVAL 30 MINUTE)
                ORDER BY T2.id ASC
                LIMIT 1
        )
    WHERE id IN (
        SELECT DISTINCT T1.Id
            FROM table T1
            INNER JOIN table T2 ON T2.cid = T1.cid
                AND T2.job_date = T1.job_date
                AND T2.id > T1.id
                AND T2.start_time <= DATE_ADD(T1.end_time, INTERVAL 30 MINUTE)
            WHERE T1.job_date = @date
        );

It was a little complex though, and not that efficient. But since this is just a query, I think you will need to update the end_time every time (daily/weekly/etc).

虽然有点复杂,但并不那么有效。但由于这只是一个查询,我认为你需要每次更新end_time(每日/每周/等)。

And make sure you set the value for the @date variable, or you could just replace it with your desired date.

并确保设置@date变量的值,或者您可以将其替换为所需的日期。

I'm thinking that using CURSOR (this is slow) will be more efficient than my query above.

我认为使用CURSOR(这很慢)将比我上面的查询更有效。

Here is a sample CURSOR query:

以下是CURSOR查询示例:

DECLARE id INT(11);
DECLARE newEndDate DATETIME;

DECLARE noMoreRows BIT(1);
DECLARE customCursor CURSOR FOR
    SELECT T1.Id
        , CAST(SUBSTRING_INDEX(GROUP_CONCAT(T2.start_date ORDER BY T2.Id ASC), ',', 1) AS DATETIME) AS NewEndDate
        FROM table T1
        INNER JOIN table T2 ON T2.cid = T1.cid
             AND T2.job_date = T1.job_date
             AND T2.id > T1.id
             AND T2.start_time <= DATE_ADD(T1.end_time, INTERVAL 30 MINUTE)
        WHERE T1.job_date = @date
        GROUP BY T1.Id
        HAVING NewEndDate <> '' OR NewEndDate IS NOT NULL;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET noMoreRows = 1;

OPEN customCursor;
customLoop: LOOP

    FETCH customCursor INTO id, newEndDate;

    IF noMoreRows THEN
      LEAVE customLoop;
   END IF;

    UPDATE table
        SET end_date = newEndDate
        WHERE id = id;

END LOOP customLoop;
CLOSE customCursor;

Just take note that CURSOR should be inside a STORED PROCEDURE. Here is the link for CURSORS.

请注意,CURSOR应该在STORED PROCEDURE中。这是CURSORS的链接。