SQLにおける日時検索

毎日毎時間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 |
+---------------------+---------+--------+

jun68ykt

2018/01/12 01:23

ご質問の中に、「毎日毎時間10分単位で」とありますが、「10分単位」ということは、毎時、00分、10分、20分、30分、40分、50分に測定されるということでしょうか？私の回答では変数を使っていますが、わざわざ変数を使うこともなかったかもしれませんね。変数を使わないとしても、「30分毎のレコード」や「1時間毎のレコード」「3時間毎のレコード」を select するのは、回答に書いたSQL と同様のクエリでいけると思います。何か疑問点等あれば回答のほうにコメントいただければと思います。

退会済みユーザー

2018/01/12 02:24

CentOSタグは無関係なので削除して欲しい。

RADIUS

2018/01/12 11:01

表示が崩れてしまいましたが、テーブル内はこの様になっています。質問内で分かりにくい表現がありすみませんでした。

jun68ykt

2018/01/12 11:49

なるほどです。そのように分が、00, 10, 20, 30, 40, 50 と丁度の数で 10 分おきになっているわけですね。少し難しく考えすぎていました。さきほど回答を更新して、追記2 に回答を書きました。変数は使いません。望ましい結果が得られない場合は、回答のコメントからお知らせください。

jun68ykt

2018/01/13 02:22

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

RADIUS

2018/01/14 13:27

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

jun68ykt

2018/01/14 13:31

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

行動規範の内容に同意します

回答1件

ベストアンサー

こんにちは。

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

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

１時間おき：

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

sql
1SET @m = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
2
3SELECT * FROM Wind 
4  WHERE MINUTE(DateTime) = @m
5ORDER BY DateTime;

** 30分おき：**

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

sql
1SET @m1 = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
2SET @m2 = @m1 + 30;
3SET @m2 = IF(@m2 > 59, @m2 - 60, @m2);
4
5SELECT * FROM Wind  
6    WHERE MINUTE(DateTime) IN (@m1, @m2)
7ORDER BY DateTime;

　
** ３時間おき：**

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

sql
1SET @h = (SELECT HOUR(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
2SET @m = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
3
4SELECT * FROM Wind 
5    WHERE 
6        MOD(HOUR(DateTime),3)  = MOD(@h,3) 
7            AND 
8        MINUTE(DateTime) = @m
9ORDER BY DateTime;

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

sql
1[ykt68@macbook15 tera108561]$ mysql -u root -p --socket /Applications/MAMP/tmp/mysql/mysql.sock tera108561
2Enter password: 
3Reading table information for completion of table and column names
4You can turn off this feature to get a quicker startup with -A
5
6Welcome to the MySQL monitor.  Commands end with ; or \g.
7Your MySQL connection id is 685
8Server version: 5.6.35 MySQL Community Server (GPL)
9
10Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
11
12Oracle is a registered trademark of Oracle Corporation and/or its
13affiliates. Other names may be trademarks of their respective
14owners.
15
16Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
17
18mysql> SHOW TABLES;
19+----------------------+
20| Tables_in_tera108561 |
21+----------------------+
22| Wind                 |
23+----------------------+
241 row in set (0.00 sec)
25
26mysql> SELECT COUNT(*) FROM Wind;
27+----------+
28| COUNT(*) |
29+----------+
30|      100 |
31+----------+
321 row in set (0.00 sec)
33
34mysql> SELECT * FROM Wind ORDER BY DateTime LIMIT 5;
35+---------------------+---------+--------+
36| DateTime            | WindDir | AirVol |
37+---------------------+---------+--------+
38| 2018-01-11 19:31:03 |     133 |    123 |
39| 2018-01-11 19:41:24 |     206 |    297 |
40| 2018-01-11 19:51:33 |     196 |    154 |
41| 2018-01-11 20:01:44 |     108 |    241 |
42| 2018-01-11 20:11:42 |     283 |    192 |
43+---------------------+---------+--------+
445 rows in set (0.00 sec)
45
46mysql> SET @m = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
47
48mysql> SELECT * FROM Wind WHERE MINUTE(DateTime) = @m ORDER BY DateTime;
49+---------------------+---------+--------+
50| DateTime            | WindDir | AirVol |
51+---------------------+---------+--------+
52| 2018-01-11 19:31:03 |     133 |    123 |
53| 2018-01-11 20:31:32 |     283 |    293 |
54| 2018-01-11 21:31:38 |     275 |    200 |
55| 2018-01-11 22:31:57 |     140 |    112 |
56| 2018-01-11 23:31:25 |     273 |    150 |
57| 2018-01-12 00:31:08 |     114 |    283 |
58| 2018-01-12 01:31:35 |     117 |    171 |
59| 2018-01-12 02:31:39 |     106 |    263 |
60| 2018-01-12 03:31:39 |     119 |    213 |
61| 2018-01-12 04:31:23 |     194 |    255 |
62| 2018-01-12 05:31:58 |     141 |    166 |
63| 2018-01-12 06:31:14 |     160 |    244 |
64| 2018-01-12 07:31:47 |     250 |    265 |
65| 2018-01-12 08:31:58 |     170 |    118 |
66| 2018-01-12 09:31:31 |     257 |    209 |
67| 2018-01-12 10:31:41 |     240 |    269 |
68| 2018-01-12 11:31:59 |     291 |    239 |
69+---------------------+---------+--------+
7017 rows in set (0.00 sec)
71
72mysql> SET @m1 = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
73Query OK, 0 rows affected (0.00 sec)
74
75mysql> SET @m2 = @m1 + 30;
76Query OK, 0 rows affected (0.00 sec)
77
78mysql> SET @m2 = IF(@m2 > 59, @m2 - 60, @m2);
79Query OK, 0 rows affected (0.00 sec)
80
81mysql> SELECT * FROM Wind WHERE MINUTE(DateTime) IN ( @m1, @m2) ORDER BY DateTime;
82+---------------------+---------+--------+
83| DateTime            | WindDir | AirVol |
84+---------------------+---------+--------+
85| 2018-01-11 19:31:03 |     133 |    123 |
86| 2018-01-11 20:01:44 |     108 |    241 |
87| 2018-01-11 20:31:32 |     283 |    293 |
88| 2018-01-11 21:01:20 |     253 |    258 |
89| 2018-01-11 21:31:38 |     275 |    200 |
90| 2018-01-11 22:01:42 |     245 |    148 |
91| 2018-01-11 22:31:57 |     140 |    112 |
92| 2018-01-11 23:01:14 |     101 |    188 |
93| 2018-01-11 23:31:25 |     273 |    150 |
94| 2018-01-12 00:01:11 |     129 |    158 |
95| 2018-01-12 00:31:08 |     114 |    283 |
96| 2018-01-12 01:01:47 |     298 |    201 |
97| 2018-01-12 01:31:35 |     117 |    171 |
98| 2018-01-12 02:01:50 |     189 |    228 |
99| 2018-01-12 02:31:39 |     106 |    263 |
100| 2018-01-12 03:01:06 |     191 |    142 |
101| 2018-01-12 03:31:39 |     119 |    213 |
102| 2018-01-12 04:01:17 |     285 |    139 |
103| 2018-01-12 04:31:23 |     194 |    255 |
104| 2018-01-12 05:01:56 |     287 |    206 |
105| 2018-01-12 05:31:58 |     141 |    166 |
106| 2018-01-12 06:01:46 |     204 |    119 |
107| 2018-01-12 06:31:14 |     160 |    244 |
108| 2018-01-12 07:01:03 |     217 |    154 |
109| 2018-01-12 07:31:47 |     250 |    265 |
110| 2018-01-12 08:01:31 |     231 |    276 |
111| 2018-01-12 08:31:58 |     170 |    118 |
112| 2018-01-12 09:01:32 |     182 |    167 |
113| 2018-01-12 09:31:31 |     257 |    209 |
114| 2018-01-12 10:01:02 |     163 |    175 |
115| 2018-01-12 10:31:41 |     240 |    269 |
116| 2018-01-12 11:01:43 |     198 |    214 |
117| 2018-01-12 11:31:59 |     291 |    239 |
118| 2018-01-12 12:01:19 |     273 |    135 |
119+---------------------+---------+--------+
12034 rows in set (0.00 sec)
121
122mysql> SET @h = (SELECT HOUR(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
123Query OK, 0 rows affected (0.00 sec)
124
125mysql> SET @m = (SELECT MINUTE(DateTime) FROM Wind ORDER BY DateTime LIMIT 1);
126Query OK, 0 rows affected (0.00 sec)
127
128mysql> SELECT * FROM Wind 
129    ->     WHERE 
130    ->         MOD(HOUR(DateTime),3)  = MOD(@h,3) 
131    ->             AND 
132    ->         MINUTE(DateTime) = @m
133    -> ORDER BY DateTime;
134+---------------------+---------+--------+
135| DateTime            | WindDir | AirVol |
136+---------------------+---------+--------+
137| 2018-01-11 19:31:03 |     133 |    123 |
138| 2018-01-11 22:31:57 |     140 |    112 |
139| 2018-01-12 01:31:35 |     117 |    171 |
140| 2018-01-12 04:31:23 |     194 |    255 |
141| 2018-01-12 07:31:47 |     250 |    265 |
142| 2018-01-12 10:31:41 |     240 |    269 |
143+---------------------+---------+--------+
1446 rows in set (0.00 sec)
145
146mysql> exit
147Bye
148[ykt68@macbook15 tera108561]$
149

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

追記

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

30分おき：

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

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

　

３時間おき：

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

sql
1SET @datetime = (SELECT DateTime FROM Wind ORDER BY DateTime LIMIT 1);
2
3SELECT * FROM Wind 
4    WHERE 
5        MOD(HOUR(DateTime),3)  = MOD(HOUR(@datetime),3) 
6            AND 
7        MINUTE(DateTime) = MINUTE(@datetime) 
8ORDER BY DateTime;

追記2

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

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

sql
1SELECT * FROM Wind 
2  WHERE MINUTE(DateTime) = 0
3ORDER BY DateTime;

30分ごと（X時 00分とX時30分）のレコードを検索

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

３時間ごと( 17:30 をスタートとして）

sql
1SELECT * FROM Wind 
2  WHERE 
3    MOD(HOUR(DateTime),3)  = MOD(17, 3) 
4      AND 
5    MINUTE(DateTime) = 30
6ORDER BY DateTime;

追記3

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

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

SQL
1SELECT * FROM Wind 
2WHERE 
3  MOD(UNIX_TIMESTAMP(DateTime) - UNIX_TIMESTAMP('2018-01-12 17:30:00'), 30 * 60) = 0 
4ORDER 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分を秒数に換算する式です。
ですので、１時間おきなら、60 * 60 、３時間おきなら 3 * 60 * 60 とします。

投稿2018/01/11 21:05

編集2018/01/13 02:15

jun68ykt

総合スコア9058

RADIUS

2018/01/12 13:25

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

jun68ykt

2018/01/12 13:41

まず、17:30 をスタートの時刻と設定するのは質問に追記された、テーブルのレコードの中で一番古いもののDateTime の時刻が 17:30 だからです。これは単なる決めです。それで、17時から始めて、3時間後の時(Hour) の数を次々に並べると、 17, 20, 23, 2, 5, 8, 11, 14, 17, 20 ・・・となりますが、これらの数の共通点は、３で割ると 2 余ることです。 WHERE条件の MOD(HOUR(DateTime),3) = MOD(17, 3) は、DateTimeカラムの時(HOUR) の数がこの条件、「17を3で割ったときと同じく、３で割ると２余る」を満たすという条件を表しています。

行動規範の内容に同意します