使用从MySQL TIME字段检索的值计算时间差

I've done a bit of searching and there is a bunch of information on calculating time difference between two times using strtotime('09:00:00) and then putting this against another value.

我已经做了一些搜索，有很多关于使用strtotime（'09：00：00）计算两次之间的时间差的信息，然后把它放在另一个值上。

Currently, I have a database that stores regular hours for staff like so:

目前，我有一个数据库，可以为员工存储正常工作时间，如下所示：

+----------+-----------+------------+-------------+--------------+
| staff_id |    day    | start_time | finish_time | total_breaks |
+----------+-----------+------------+-------------+--------------+
|        1 | Monday    | 18:00:00   | 22:00:00    |            0 |
|        2 | Wednesday | 09:00:00   | 17:30:00    |           30 |
+----------+-----------+------------+-------------+--------------+

start_time and finish_time are stored as TIME values in MySQL and total_breaks as an INT which I should probably convert to TIME values as well and represent as 00:30:00 or 00:00:00 for consistency.

start_time和finish_time在MySQL中存储为TIME值，在total_breaks中存储为INT，我也应将其转换为TIME值，并表示为00:30:00或00:00:00以保持一致性。

What I want to be able to do is display a table that displays all of this information, as well as their total hours worked and then total hours minus any breaks.

我想要做的是显示一个表格，显示所有这些信息，以及他们的总工作时间，然后总小时数减去任何休息时间。

I've been able to generate a table with the staff_id, day, start_time and finish_time but am struggling to figure out the best way to calculate the total amount of time worked etc.

我已经能够使用staff_id，day，start_time和finish_time生成一个表，但我正在努力找出计算总工作时间等的最佳方法。

I'm using Laravel, and have read that strtotime('00:00:00') is a great way to do this but this won't work as I'm not using a string but rather pulling the time from the database.

我正在使用Laravel，并且已经读过strtotime（'00：00：00'）这是一个很好的方法，但这不会起作用，因为我没有使用字符串，而是从数据库中抽出时间。

What is the best way to calculate the time difference between the two times, and then take in to account the breaks as well.

计算两次之间的时差的最佳方法是什么，然后考虑休息时间。

Have I even set this up correctly? I don't want to set the times up as a DATETIME as I'm just wanting to calculate the time difference of their normal working hours which they do from week to week - although I can always use dummy dates to get this working if necessary.

我是否正确设置了这个？我不想将时间设置为DATETIME，因为我只是想计算他们每周做的正常工作时间的时差 - 尽管我总是可以使用虚拟日期来使其工作在必要时。

Any ideas?

有任何想法吗？

2 个解决方案

#1

No need to make things overly complicated.

不需要让事情变得过于复杂。

You can always convert your database times to epoch time using strtotime

您始终可以使用strtotime将数据库时间转换为纪元时间

<?php
$start_time = '2015-09-23 22:00:00'; // pulled from DB
$finish_time = '2015-09-24 06:00:00'; // pulled from DB
$starttime = strtotime($start_time); // convert to timestring
$endtime = strtotime($finish_time); // convert to timestring
$diff = $endtime - $starttime; // do the math

$total_breaks = 30; // pulled from DB
$breaks = $total_breaks*60; // minutes * seconds per minute
$hours = ($diff - $breaks)/60/60; // do the math converting seconds to hours
?>

The above code will output like this...

上面的代码会像这样输出......

<?php
echo 'clocked in: '.$start_time.'<br>';
echo 'clocked out: '.$finish_time.'<br>';
echo 'breaks: '.$total_breaks.' minutes<br>';
echo 'hours worked: '.number_format($hours, 2).'<br>';
?>

clocked in: 2015-09-23 22:00:00
clocked out: 2015-09-24 06:00:00
breaks: 30 minutes
hours worked: 7.50

主要时间：2015-09-23 22:00:00发出时间：2015-09-24 06:00:00休息时间：30分钟工作时间：7.50

Suggestion: Change your start_time and finish_time fields to DATETIME so you won't have a problem if someone works through midnight to the next day like in the code I used.

建议：将start_time和finish_time字段更改为DATETIME，这样如果有人在午夜到第二天工作，就像我使用的代码一样，你就不会有问题。

Happy Coding !

快乐的编码！

NOTE: If you use strtotime without a date specified like strtotime(09:00:00) ... the current date will be automatically added to form a complete DateTime. BUT if anyone works through midnight the calculation will yield a negative number.

注意：如果您使用没有指定日期（如09:00:00）的strtotime ...将自动添加当前日期以形成完整的DateTime。但如果有人在午夜工作，计算将产生负数。

IF no one works through midnight you can just use the Time as your data like so...

如果没有人在午夜工作，您可以使用时间作为您的数据，如此...

<?php
$start_time = '09:00:00';
$finish_time = '17:00:00';
$starttime = strtotime($start_time);
$endtime = strtotime($finish_time);
etc...
?>

#2

Not sure about a few things in your question, so I'll start with a query to begin and we can increase whats in here as we move on.

不确定你的问题中的一些事情，所以我将从一个查询开始，我们可以在这里继续增加什么。

mysql> SELECT TIMEDIFF( '09:00:00', '17:30:00' );
+------------------------------------+
| TIMEDIFF( '09:00:00', '17:30:00' ) |
+------------------------------------+
| -08:30:00                          |
+------------------------------------+
1 row in set (0.00 sec)

You could include that to work out the total time from start to finish ... TIMEDIFF( start_time, finish_time ) AS total_time.

你可以用它来计算从开始到结束的总时间...... TIMEDIFF（start_time，finish_time）AS total_time。

Then, we need to deduct the total minutes from that value.

然后，我们需要从该值中扣除总分钟数。

Once we know how many minutes have been taken as breaks ...

一旦我们知道休息了多少分钟......

mysql> select ADDTIME('08:00:00', '00:30:00');
+---------------------------------+
| ADDTIME('08:00:00', '00:30:00') |
+---------------------------------+
| 08:30:00                        |
+---------------------------------+
1 row in set (0.00 sec)

How does that look?

那怎么样？

Ok, so doing a similar calculation in PHP is easy.

好的，所以在PHP中进行类似的计算很容易。

$start_time = new DateTime($your_start_time);
$finish_time = new DateTime($your_finish_time);
$diff = $start_time->Diff($finish_time);
$hours = $diff->format("%H");

Also, when you have finish_time. You can remove time from it (based on breaks) like so (30 is 30 mins);

此外，当你有finish_time。您可以从中删除时间（基于休息时间）（30分钟为30分钟）;

$finish_time->sub(new DateInterval('PT30M'));

#1