MySQLの日付・日時をテストデータ用にランダム生成 #MySQL

参考

MySQLの関数を使用する

日付を扱う関数

ADDDATE(date,INTERVAL expr unit)
ADDDATE(expr,days)

mysql> SELECT ADDDATE('2008-01-02', 31);
// 結果 → '2008-02-02'

mysql> SELECT ADDDATE('2008-01-02', 31);
// 結果 → '2008-02-02'

第１引数：基準の日付
第２引数：基準の日付に加算する日数

第２引数に渡す日数をランダム数にする

mysql> SELECT ADDDATE('2008-01-02', 31*rand());

[結果]
2021-02-06
2021-02-08
2021-02-20

rand()は0 < rand() < 1.0の範囲のランダムな浮動小数点値を生成するので、

0 <= rand() * 31 < 31
0 <= rand() * 100 < 100

という感じになります。

注意点としては右の不等号はイコールが含まれないので、整数で取得することを考えると実質は次のようになります

0 <= rand() * 31 <= 30
0 <= rand() * 100 <= 99

こっちのほうがわかりやすいですよね～

日時を扱う関数

ADDTIME(expr1,expr2)

ADDTIME() は expr2 と expr1 を加算し、その結果を返します。expr1 は時間または日付時間式であり、expr2 は時間式です。

mysql> SELECT ADDTIME('2007-12-31 23:59:59.999999', '1 1:1:1.000002');
        -> '2008-01-02 01:01:01.000001'
mysql> SELECT ADDTIME('01:00:00.999999', '02:00:00.999998');
        -> '03:00:01.999997'

expr2は時間式ということですが、整数でも渡せます。

SELECT ADDTIME('2021-02-01 00:00:00', 100);
 -> 2021-02-01 00:01:00

SELECT ADDTIME('2021-02-01 00:00:00', 10000);
 -> 2021-02-01 01:00:00

125812 -> 12:58:12
のように変換されて加算してくれる

しかし秒数で加算したほうが感覚的にはわかりやすいですよね。というときにはSEC_TO_TIMEを使います。

SELECT ADDTIME('2021-02-01 00:00:00', SEC_TO_TIME(10000));
 -> 2021-02-01 02:46:40

引数の10000を秒として処理してくれるので、
10000秒 -> 2時間46分40秒に変換してくれる

わかりやすく3600秒（1時間）を渡してあげると
SELECT ADDTIME('2021-02-01 00:00:00', SEC_TO_TIME(3600));
 -> 2021-02-01 01:00:00

後はSEC_TO_TIMEにランダム数を渡して上げれば良い

SELECT ADDTIME('2021-02-01 00:00:00', SEC_TO_TIME(floor(rand() * 3600)));
 -> 2021-02-01 00:39:05
※floorで整数を渡すようにしています

rand() * 3600 で一時間以内の時間を返してくれます。

rand() * 3600 * 24 なら２４時間以内ですね。

１年とかにしたいけども、限界値を超えるのでできないみたいです。

秒で渡すのではなく、日数も渡せるはずなので可能だと思います。（まだ不勉強）

とりあえず簡易的なテストデータとして日付や日時を扱う分はある程度範囲が限定されていても良いかな思います。

ちなみ・・・

javaからPreparedStatementでパラメータを渡すときに


ps.setString("SELECT ADDTIME('2021-02-01 00:00:00', SEC_TO_TIME(floor(rand() * 3600)))");

としてもうまくいきませんでした。value (?)のように?に後から渡すんじゃなくて、
SQL分にそのまま文字列で書いちゃえば実行できました。

原因というか、なぜなのかよくわかりませんが。。。分かる方がいらっしゃいましたらコメントお願いしますm(__)m