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);
  }

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

2 个解决方案

#1

更多相关文章

随机推荐