php - MySQLクエリ中の時間差を計算する

原文 php mysql datetime

2つのMySQLテーブルがあり、どちらにもDATETIMEフィールドが含まれています。

テーブルbeginningsには、特定のプロセスが開始された時刻が含まれています
テーブルeventsには、特定のイベントがそのプロセスによって発生した時刻が含まれています

初めは夕方です。イベントは翌朝に発生します。各開始の後に複数のイベントの数が変化することに注意してください!
以下は2つのサンプルテーブルです。
CREATE TABLE IF NOT EXISTS `beginnings` (
  `bid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `begin` datetime NOT NULL,
  PRIMARY KEY (`bid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

INSERT INTO `beginnings` (`bid`, `begin`) VALUES
(1, '2014-09-24 22:44:00'),
(2, '2014-09-25 21:32:00'),
(3, '2014-09-28 22:01:00');

CREATE TABLE IF NOT EXISTS `events` (
  `eid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `event` datetime NOT NULL,
  PRIMARY KEY (`eid`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=10 ;

INSERT INTO `events` (`eid`, `event`) VALUES
(1, '2014-09-25 02:07:00'),
(2, '2014-09-25 04:06:00'),
(3, '2014-09-25 05:50:00'),
(4, '2014-09-26 02:07:00'),
(5, '2014-09-26 04:15:00'),
(7, '2014-09-29 01:08:00'),
(8, '2014-09-29 04:21:00'),
(6, '2014-09-29 05:02:00'),
(9, '2014-09-29 05:25:00');

各イベントとそれぞれの最初の時間差(分単位)を計算します。
現在、データベースからデータを取得し、日時エントリをタイムスタンプに変換して、PHPでの違いを計算しています。以下は、読みやすいように、数字を構成した簡略化されたサンプルコードです。
$events = range(1, 50);
$beginnings = array(7, 11, 19, 27, 29, 36, 40, 43);
$events = array_merge(array_diff($events, $beginnings));

$differences = array();
for($i = 0; $i < count($beginnings); $i++) {
    foreach($events as $event) {
        if($event > $beginnings[$i] && $event < $beginnings[$i + 1]) {
            $differences[] = $event - $beginnings[$i];
        }
    }
}

私の質問:
クエリ中にMySQLでこれらの違いを計算することは可能ですか?

実例:
あなたは自動車工場のボスです。毎晩、就寝する前に、夜間シフトのチームに、各車を1台製造するように伝え、完成したときをメモします。午前中に戻ってきたら、各チームがそれぞれの車を組み立てるのにかかった時間を計算します。あるチームが2時間で車を完成させたのに対し、別のチームは車を完成させるのに5時間を要したことがわかります。あなたは好奇心が強いので、1年の時間を記録し続け、いくつかの統計を行います。
答え
まあ、それが私なら、おそらくこのようなことをするでしょう...

DROP TABLE project_events;

CREATE TABLE project_events
(project_id INT NOT NULL
,event_date DATETIME NOT NULL
);

INSERT INTO project_events VALUES
(1, '2014-09-24 22:44:00'),
(1, '2014-09-25 02:07:00'),
(1, '2014-09-25 04:06:00'),
(1, '2014-09-25 05:50:00'),

(2, '2014-09-25 21:32:00'),
(2, '2014-09-26 02:07:00'),
(2, '2014-09-26 04:15:00'),

(3, '2014-09-28 22:01:00'),
(3, '2014-09-29 01:08:00'),
(3, '2014-09-29 04:21:00'),
(3, '2014-09-29 05:02:00'),
(3, '2014-09-29 05:25:00');

SELECT project_id
     , MIN(event_date) start
     , MAX(event_date) end
     , TIMEDIFF(MAX(event_date)
     , MIN(event_date)) duration 
  FROM project_events 
 GROUP 
    BY project_id;
+------------+---------------------+---------------------+----------+
| project_id | start               | end                 | duration |
+------------+---------------------+---------------------+----------+
|          1 | 2014-09-24 22:44:00 | 2014-09-25 05:50:00 | 07:06:00 |
|          2 | 2014-09-25 21:32:00 | 2014-09-26 04:15:00 | 06:43:00 |
|          3 | 2014-09-28 22:01:00 | 2014-09-29 05:25:00 | 07:24:00 |
+------------+---------------------+---------------------+----------+


このスキーマにより、プロジェクトがオーバーランまたはオーバーラップする可能性があります。 「始まり」を別のテーブルに保存したい正当な理由があるかもしれませんが、それを考慮してこのソリューションを適応させるのは簡単です。これは単に、project_idでの結合を含みます。

編集:以下は、そのプロジェクトの開始日とそれ以降のすべての日付を比較する1つの方法です。

SELECT x.project_id
     , MIN(y.event_date) start
     , x.event_date end
     , TIMEDIFF(x.event_date,MIN(y.event_date)) duration 
  FROM project_events x 
  JOIN project_events y 
    ON y.project_id = x.project_id 
 GROUP 
    BY x.project_id,x.event_date;
+------------+---------------------+---------------------+----------+
| project_id | start               | end                 | duration |
+------------+---------------------+---------------------+----------+
|          1 | 2014-09-24 22:44:00 | 2014-09-24 22:44:00 | 00:00:00 |
|          1 | 2014-09-24 22:44:00 | 2014-09-25 02:07:00 | 03:23:00 |
|          1 | 2014-09-24 22:44:00 | 2014-09-25 04:06:00 | 05:22:00 |
|          1 | 2014-09-24 22:44:00 | 2014-09-25 05:50:00 | 07:06:00 |
|          2 | 2014-09-25 21:32:00 | 2014-09-25 21:32:00 | 00:00:00 |
|          2 | 2014-09-25 21:32:00 | 2014-09-26 02:07:00 | 04:35:00 |
|          2 | 2014-09-25 21:32:00 | 2014-09-26 04:15:00 | 06:43:00 |
|          3 | 2014-09-28 22:01:00 | 2014-09-28 22:01:00 | 00:00:00 |
|          3 | 2014-09-28 22:01:00 | 2014-09-29 01:08:00 | 03:07:00 |
|          3 | 2014-09-28 22:01:00 | 2014-09-29 04:21:00 | 06:20:00 |
|          3 | 2014-09-28 22:01:00 | 2014-09-29 05:02:00 | 07:01:00 |
|          3 | 2014-09-28 22:01:00 | 2014-09-29 05:25:00 | 07:24:00 |
+------------+---------------------+---------------------+----------+
関連記事

php - 別の配列に基づいて配列をキーでソートしますか?

php - PHPでCurlを使用したWebクロール

php - Yiiモジュールが機能しない

php - LaravelでZend Feed Readerを使用する方法

php - jquery Datatablesの同じ結果

php - 偽の配列値を偽の境界外インデックスと区別するにはどうすればよいですか?

javascript - 入力タイプを確認し、[保存]をクリックしている間は入力「パスワード」フィールドをスキップしてください

javascript - Angular Save vars to laravel session with ajax call do not work

php - ZF2 setPartialサブメニューにパラメーターを追加

php - カスタムインストールされたPHPをデフォルトとしてロードする