質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.51%

  • MariaDB

    295questions

    MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。

SQLにおける日時検索

解決済

回答 1

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 263

RADIUS

score 3

イメージ説明

毎日毎時間10分単位で時間と風向・風量を測定しDBに記録をしていく様な、添付した画像のテーブルがあります。

そこで質問ですが、このテーブルから
「30分毎のレコード」や「1時間毎のレコード」「3時間毎のレコード」
など間隔をあけて抽出する場合どのようなSQLを組めば良いのでしょうか?

よろしくお願い致します。

===追記=================================================================
テーブル内はこの様な感じになっています。
また、秒は誤差とみなしてレコード追加時に切り捨てています。
+---------------------+---------+--------+
| DateTime            | WindDir | AirVol |
+---------------------+---------+--------+
| 2018-01-12 17:30:00 |     200 |     30 |
| 2018-01-12 17:40:00 |     200 |     30 |
| 2018-01-12 17:50:00 |     200 |     30 |
| 2018-01-12 18:00:00 |     250 |     40 |
| 2018-01-12 18:10:00 |     230 |     35 |
| 2018-01-12 18:20:00 |     200 |     35 |
| 2018-01-12 18:30:00 |     200 |     30 |
+---------------------+---------+--------+

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • jun68ykt

    2018/01/13 11:22

    こんにちは。回答に追記3を書きました。30分ごと、1時間ごと、3時間ごとのいずれも同じSQLで、時間間隔のパラメータだけを変えればよいようなクエリです。参考になれば幸いです。

    キャンセル

  • RADIUS

    2018/01/14 22:27

    ご回答ありがとうございます。返事が遅くなってしまいすみませんでした。2回にわたりクエリを考えて頂きありがとうございました。おかげさまで私の意図した結果を抽出することが出来、解決をすることが出来ました。 今回はありがとうございました。

    キャンセル

  • jun68ykt

    2018/01/14 22:31

    解決されたとのことで、よかったです!

    キャンセル

回答 1

checkベストアンサー

+1

こんにちは。

MariaDBはMySQLと互換性があるものと考え、以下、私の手元にある MySQL(バージョン 5.6)で
検証しています。

MySQL だと、以下のように、ユーザー定義変数を使えば出来ました。

1時間おき:

時系列で一番古いレコードの DateTime の分を変数 @m に代入し、@m と分が同じ
レコードを select します。

SET @m = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);

SELECT * FROM Wind 
  WHERE MINUTE(DateTime) = @m
ORDER BY DateTime;


 30分おき:

時系列で一番古いレコードの DateTime の分を変数 @m1 に代入し、さらに
@m1 から 30分後を表す分を @m2 に代入して、@m1 または @m2 と分が同じ
レコードを select します。

SET @m1 = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
SET @m2 = @m1 + 30;
SET @m2 = IF(@m2 > 59, @m2 - 60, @m2);

SELECT * FROM Wind  
    WHERE MINUTE(DateTime) IN (@m1, @m2)
ORDER BY DateTime;


 
 3時間おき:
 
時系列で一番古いレコードの DateTime の時、分を、@h および @m に
代入し、時を3で割った余りが、@h を3で割った余りと一致し、かつ
分が @m と一致するレコードをselectします。

SET @h = (SELECT HOUR(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
SET @m = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);

SELECT * FROM Wind 
    WHERE 
        MOD(HOUR(DateTime),3)  = MOD(@h,3) 
            AND 
        MINUTE(DateTime) = @m
ORDER BY DateTime;

  
以下は、MySQL 5.6 にて、100レコード入っているテーブルを作成して、上記のクエリを順に試したログです。
(DB名は tera108561、テーブル名は Wind としています。WindDirとAirVolにはランダムな数を入れています。)

[ykt68@macbook15 tera108561]$ mysql -u root -p --socket /Applications/MAMP/tmp/mysql/mysql.sock tera108561
Enter password: 
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 685
Server version: 5.6.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SHOW TABLES;
+----------------------+
| Tables_in_tera108561 |
+----------------------+
| Wind                 |
+----------------------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM Wind;
+----------+
| COUNT(*) |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM Wind ORDER BY DateTime LIMIT 5;
+---------------------+---------+--------+
| DateTime            | WindDir | AirVol |
+---------------------+---------+--------+
| 2018-01-11 19:31:03 |     133 |    123 |
| 2018-01-11 19:41:24 |     206 |    297 |
| 2018-01-11 19:51:33 |     196 |    154 |
| 2018-01-11 20:01:44 |     108 |    241 |
| 2018-01-11 20:11:42 |     283 |    192 |
+---------------------+---------+--------+
5 rows in set (0.00 sec)

mysql> SET @m = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);

mysql> SELECT * FROM Wind WHERE MINUTE(DateTime) = @m ORDER BY DateTime;
+---------------------+---------+--------+
| DateTime            | WindDir | AirVol |
+---------------------+---------+--------+
| 2018-01-11 19:31:03 |     133 |    123 |
| 2018-01-11 20:31:32 |     283 |    293 |
| 2018-01-11 21:31:38 |     275 |    200 |
| 2018-01-11 22:31:57 |     140 |    112 |
| 2018-01-11 23:31:25 |     273 |    150 |
| 2018-01-12 00:31:08 |     114 |    283 |
| 2018-01-12 01:31:35 |     117 |    171 |
| 2018-01-12 02:31:39 |     106 |    263 |
| 2018-01-12 03:31:39 |     119 |    213 |
| 2018-01-12 04:31:23 |     194 |    255 |
| 2018-01-12 05:31:58 |     141 |    166 |
| 2018-01-12 06:31:14 |     160 |    244 |
| 2018-01-12 07:31:47 |     250 |    265 |
| 2018-01-12 08:31:58 |     170 |    118 |
| 2018-01-12 09:31:31 |     257 |    209 |
| 2018-01-12 10:31:41 |     240 |    269 |
| 2018-01-12 11:31:59 |     291 |    239 |
+---------------------+---------+--------+
17 rows in set (0.00 sec)

mysql> SET @m1 = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @m2 = @m1 + 30;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @m2 = IF(@m2 > 59, @m2 - 60, @m2);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Wind WHERE MINUTE(DateTime) IN ( @m1, @m2) ORDER BY DateTime;
+---------------------+---------+--------+
| DateTime            | WindDir | AirVol |
+---------------------+---------+--------+
| 2018-01-11 19:31:03 |     133 |    123 |
| 2018-01-11 20:01:44 |     108 |    241 |
| 2018-01-11 20:31:32 |     283 |    293 |
| 2018-01-11 21:01:20 |     253 |    258 |
| 2018-01-11 21:31:38 |     275 |    200 |
| 2018-01-11 22:01:42 |     245 |    148 |
| 2018-01-11 22:31:57 |     140 |    112 |
| 2018-01-11 23:01:14 |     101 |    188 |
| 2018-01-11 23:31:25 |     273 |    150 |
| 2018-01-12 00:01:11 |     129 |    158 |
| 2018-01-12 00:31:08 |     114 |    283 |
| 2018-01-12 01:01:47 |     298 |    201 |
| 2018-01-12 01:31:35 |     117 |    171 |
| 2018-01-12 02:01:50 |     189 |    228 |
| 2018-01-12 02:31:39 |     106 |    263 |
| 2018-01-12 03:01:06 |     191 |    142 |
| 2018-01-12 03:31:39 |     119 |    213 |
| 2018-01-12 04:01:17 |     285 |    139 |
| 2018-01-12 04:31:23 |     194 |    255 |
| 2018-01-12 05:01:56 |     287 |    206 |
| 2018-01-12 05:31:58 |     141 |    166 |
| 2018-01-12 06:01:46 |     204 |    119 |
| 2018-01-12 06:31:14 |     160 |    244 |
| 2018-01-12 07:01:03 |     217 |    154 |
| 2018-01-12 07:31:47 |     250 |    265 |
| 2018-01-12 08:01:31 |     231 |    276 |
| 2018-01-12 08:31:58 |     170 |    118 |
| 2018-01-12 09:01:32 |     182 |    167 |
| 2018-01-12 09:31:31 |     257 |    209 |
| 2018-01-12 10:01:02 |     163 |    175 |
| 2018-01-12 10:31:41 |     240 |    269 |
| 2018-01-12 11:01:43 |     198 |    214 |
| 2018-01-12 11:31:59 |     291 |    239 |
| 2018-01-12 12:01:19 |     273 |    135 |
+---------------------+---------+--------+
34 rows in set (0.00 sec)

mysql> SET @h = (SELECT HOUR(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @m = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM Wind 
    ->     WHERE 
    ->         MOD(HOUR(DateTime),3)  = MOD(@h,3) 
    ->             AND 
    ->         MINUTE(DateTime) = @m
    -> ORDER BY DateTime;
+---------------------+---------+--------+
| DateTime            | WindDir | AirVol |
+---------------------+---------+--------+
| 2018-01-11 19:31:03 |     133 |    123 |
| 2018-01-11 22:31:57 |     140 |    112 |
| 2018-01-12 01:31:35 |     117 |    171 |
| 2018-01-12 04:31:23 |     194 |    255 |
| 2018-01-12 07:31:47 |     250 |    265 |
| 2018-01-12 10:31:41 |     240 |    269 |
+---------------------+---------+--------+
6 rows in set (0.00 sec)

mysql> exit
Bye
[ykt68@macbook15 tera108561]$

以上、参考になれば幸いです。


追記

上記の、変数を使った SQL は以下のようにリファクタできます。
  
30分おき: 

@m1 から @m2 を作るところは、以下の1行で済みます。

SET @m2 = IF(@m1 + 30 > 59, @m1 - 30, @m1 + 30);


 
 
3時間おき:

以下のようにすれば、変数は 1 つだけで済みます。

SET @datetime = (SELECT DateTime FROM Wind ORDER BY DateTime LIMIT 1);

SELECT * FROM Wind 
    WHERE 
        MOD(HOUR(DateTime),3)  = MOD(HOUR(@datetime),3) 
            AND 
        MINUTE(DateTime) = MINUTE(@datetime) 
ORDER BY DateTime;

追記2

質問に追加されたテーブルの内容から、以下でOKと思います。

 1時間ごと(ちょうどX時 00分)のレコードを検索

SELECT * FROM Wind 
  WHERE MINUTE(DateTime) = 0
ORDER BY DateTime;

 30分ごと(X時 00分とX時30分)のレコードを検索

SELECT * FROM Wind  
    WHERE MINUTE(DateTime) IN (0, 30)
ORDER BY DateTime;

 3時間ごと( 17:30 をスタートとして)

SELECT * FROM Wind 
  WHERE 
    MOD(HOUR(DateTime),3)  = MOD(17, 3) 
      AND 
    MINUTE(DateTime) = 30
ORDER BY DateTime;

追記3

別解を思いつきました。
以下のようにすると、30分ごと、1時間ごと、3時間ごと のいずれも同じSQLで、
時間間隔のパラメータだけを変えればよくなります。

30分ごとのレコードは以下のようなクエリで取れます。

SELECT * FROM Wind 
WHERE 
  MOD(UNIX_TIMESTAMP(DateTime) - UNIX_TIMESTAMP('2018-01-12 17:30:00'), 30 * 60) = 0 
ORDER BY DateTime;


結果:

+---------------------+---------+--------+
| DateTime            | WindDir | AirVol |
+---------------------+---------+--------+
| 2018-01-12 17:30:00 |     240 |     30 |
| 2018-01-12 18:00:00 |     240 |     45 |
| 2018-01-12 18:30:00 |     270 |     40 |
| 2018-01-12 19:00:00 |     250 |     50 |
| 2018-01-12 19:30:00 |     280 |     35 |
| 2018-01-12 20:00:00 |     230 |     40 |
・・・

上記のクエリで、 30 * 60 という掛け算がありますが、これは、30分を秒数に換算する式です。
ですので、1時間おきなら、60 * 60 、3時間おきなら 3 * 60 * 60 とします。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/01/12 22:25

    分かりやすい素早い回答ありがとうございます。
    SQLの例として教えて頂いた「3時間ごと」にある「MOD(17, 3)」にある「2」はどの様な意味があるのでしょうか?

    キャンセル

  • 2018/01/12 22:41

    まず、17:30 をスタートの時刻と設定するのは質問に追記された、
    テーブルのレコードの中で一番古いもののDateTime の時刻が
    17:30 だからです。これは単なる決めです。

    それで、17時から始めて、3時間後の時(Hour) の数を次々に並べると、

    17, 20, 23, 2, 5, 8, 11, 14, 17, 20 ・・・

    となりますが、これらの数の共通点は、3で割ると 2 余ることです。
    WHERE条件の

    MOD(HOUR(DateTime),3) = MOD(17, 3)

    は、DateTimeカラムの時(HOUR) の数がこの条件、
    「17を3で割ったときと同じく、3で割ると2余る」
    を満たすという条件を表しています。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 90.51%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る

  • MariaDB

    295questions

    MariaDBは、MySQL派生のオープンソースなリレーショナルデータベースシステムです。 また、MySQLとほぼ同じデータベースエンジンに対応しています。