MySQL时间函数

1. 获取当前时间

mysql> select now(),sleep(3),sysdate(),curdate(),curtime();
+---------------------+----------+---------------------+------------+-----------+
| now()               | sleep(3) | sysdate()           | curdate()  | curtime() |
+---------------------+----------+---------------------+------------+-----------+
| 2020-08-24 08:56:01 |        0 | 2020-08-24 08:56:04 | 2020-08-24 | 08:56:01  |
+---------------------+----------+---------------------+------------+-----------+
1 row in set (3.00 sec)

• now()：获得当前日期 + 时间（date + time）；
• sysdate()：同now()；now() 在执行开始时得到值， sysdate() 在函数执行时动态得到值；
• curdate()：获取当前日期（date）；
• curtime()：获取当前时间（time）；

2. 选取日期时间的各个部分：

日期、时间、年、季度、月、日、小时、分钟、秒、微秒

2.1 时间函数

mysql> set @dt=now();
Query OK, 0 rows affected (0.04 sec)

mysql> select @dt;
+---------------------+
| @dt                 |
+---------------------+
| 2020-08-24 09:00:17 |
+---------------------+
1 row in set (0.00 sec)

mysql>  select date(@dt),time(@at) ,year(@dt) ,quarter(@dt),month(@dt) ,week(@dt)  , day(@dt) ,hour(@dt) ;
+------------+-----------+-----------+--------------+------------+-----------+----------+-----------+
| date(@dt)  | time(@at) | year(@dt) | quarter(@dt) | month(@dt) | week(@dt) | day(@dt) | hour(@dt) |
+------------+-----------+-----------+--------------+------------+-----------+----------+-----------+
| 日期        | 时间      | 年        | 季           | 月         | 周        |       天    | 小时    |
+------------+-----------+-----------+--------------+------------+-----------+----------+-----------+
| 2020-08-24 | NULL      |      2020 |            3 |          8 |        34 |       24 |         9 |
+------------+-----------+-----------+--------------+------------+-----------+----------+-----------+
1 row in set (0.00 sec)

2.2 extract()选取函数

mysql> select extract(year from @dt), extract(quarter from @dt),extract(week from @dt),extract(minute from @dt), extract(second from @dt), extract(microsecond from @dt);
+------------------------+---------------------------+------------------------+--------------------------+--------------------------+-------------------------------+
| extract(year from @dt) | extract(quarter from @dt) | extract(week from @dt) | extract(minute from @dt) | extract(second from @dt) | extract(microsecond from @dt) |
+------------------------+---------------------------+------------------------+--------------------------+--------------------------+-------------------------------+
|                   2020 |                         3 |                     34 |                        0 |                       17 |                             0 |
+------------------------+---------------------------+------------------------+--------------------------+--------------------------+-------------------------------+
1 row in set (0.00 sec)

mysql> select extract(year_month from @dt), extract(day_hour from @dt), extract(day_minute from @dt), extract(day_second from @dt), extract(day_microsecond from @dt), extract(hour_minute from @dt), extract(hour_second from @dt), extract(hour_microsecond from @dt), extract(minute_second from @dt), extract(minute_microsecond from @dt), extract(second_microsecond from @dt);
+------------------------------+----------------------------+------------------------------+------------------------------+-----------------------------------+-------------------------------+-------------------------------+------------------------------------+---------------------------------+--------------------------------------+--------------------------------------+
| extract(year_month from @dt) | extract(day_hour from @dt) | extract(day_minute from @dt) | extract(day_second from @dt) | extract(day_microsecond from @dt) | extract(hour_minute from @dt) | extract(hour_second from @dt) | extract(hour_microsecond from @dt) | extract(minute_second from @dt) | extract(minute_microsecond from @dt) | extract(second_microsecond from @dt) |
+------------------------------+----------------------------+------------------------------+------------------------------+-----------------------------------+-------------------------------+-------------------------------+------------------------------------+---------------------------------+--------------------------------------+--------------------------------------+
|                       202008 |                       2409 |                       240900 |                     24090017 |                    24090017000000 |                           900 |                         90017 |                        90017000000 |                              17 |                             17000000 |                             17000000 |
+------------------------------+----------------------------+------------------------------+------------------------------+-----------------------------------+-------------------------------+-------------------------------+------------------------------------+---------------------------------+--------------------------------------+--------------------------------------+
1 row in set (0.00 sec)

2.3 dayofweek(), dayofmonth(), dayofyear()

分别返回日期参数，在一周、一月、一年中的位置。

https://blog.csdn.net/qq_38486203/article/details/80654040