MySQL 8随机日期/时间生成
在学习过程中,经常需要准备时间日期数据,经常用到的随机日期时间生产脚本:
1. 生成24小时内时间
有微秒值
mysql> select sec_to_time(rand() * 86400);
+-----------------------------+
| sec_to_time(rand() * 86400) |
+-----------------------------+
| 21:56:22.705685 |
+-----------------------------+
1 row in set (0.00 sec)
2.生成一天内随机时间
floor取整秒
86400 是1天的秒数
mysql> select sec_to_time(floor(rand() * 86400));
+------------------------------------+
| sec_to_time(floor(rand() * 86400)) |
+------------------------------------+
| 08:44:18 |
+------------------------------------+
1 row in set (0.00 sec)
3.生成1小时内时间
包括微秒
mysql> select sec_to_time(rand() * 3600);
+----------------------------+
| sec_to_time(rand() * 3600) |
+----------------------------+
| 00:04:40.939843 |
+----------------------------+
1 row in set (0.00 sec)
4.生成比当前时间小的90天内的随机日期
精确到天,注意负号 -90
mysql> SELECT date_add(current_date, interval rand() * -90 day) rand_date;
+------------+
| rand_date |
+------------+
| 2023-10-14 |
+------------+
1 row in set (0.00 sec)
5.生成比当前时间大的90天内的随机日期
精确到天
mysql> SELECT date_add(current_date, interval rand() * 90 day) rand_date;
+------------+
| rand_date |
+------------+
| 2023-12-03 |
+------------+
1 row in set (0.00 sec)
6.生成比当前时间小的90天内的随机时间
显示到秒实际精确到天
mysql> SELECT date_add(NOW(), interval rand() * -90 day) rand_date;
+---------------------+
| rand_date |
+---------------------+
| 2023-10-07 11:06:14 |
+---------------------+
1 row in set (0.00 sec)
7.生成比当前时间小的90天内的随机时间
精确到微秒
90243600 = 7776000 ,90天的总秒数。
mysql> SELECT date_add(current_timestamp, interval rand() * -7776000 second);
+----------------------------------------------------------------+
| date_add(current_timestamp, interval rand() * -7776000 second) |
+----------------------------------------------------------------+
| 2023-10-27 23:40:25.021294 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)
8.生成比当前时间小的90天内的随机时间
精确到秒格式化
mysql> SELECT DATE_FORMAT(date_add(current_timestamp, interval rand() * -7776000 second),'%Y-%m-%d %H:%i:%s');
+-------------------------------------------------------------------------------------------------+
| DATE_FORMAT(date_add(current_timestamp, interval rand() * -7776000 second),'%Y-%m-%d %H:%i:%s') |
+-------------------------------------------------------------------------------------------------+
| 2023-09-17 12:52:17 |
+-------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)