MYSQL的日期时间函数

获取当前日期时间–now()
sysdate() 日期时间函数跟 now() 类似，不同之处在于：now() 在执行开始时值就得到了， sysdate() 在函数执行时动态得到值。

mysql> select now(), sleep(3), now();

+---------------------+----------+---------------------+
| now() | sleep(3) | now() |
+---------------------+----------+---------------------+
| 2020-03-04 13:43:32 | 0 | 2020-03-04 13:43:32 |
+---------------------+----------+---------------------+

mysql> select sysdate(), sleep(3), sysdate();

+---------------------+----------+---------------------+
| sysdate() | sleep(3) | sysdate() |
+---------------------+----------+---------------------+
|2020-03-04 13:43:32 | 0 | 2020-03-04 13:43:35 |
+---------------------+----------+---------------------+

获取当前日期 –curdate() 等同于current_date() ,current_date

select curdate();

+------------+
| curdate() |
+------------+
| 2020-03-04 |
+------------+

获取当前时间 –curtime()等同于current_time() ,current_time

select curtime();

+------------+
| curtime() |
+------------+
| 13:52:20 |
+------------+

获取月份中的最后一天–last_day(时间)

select last_day(now());

+------------+
| last_day(now()) |
+------------+
| 2020-03-31 |
+------------+

获取当月第一天

select date_sub(curdate(),interval day(curdate())-1 day) as '当月第一天'

+------------+
| 当月第一天 |
+------------+
|2020-03-01 |
+------------+

获取当年的第一天

select date_sub(curdate(),interval dayofyear(curdate())-1 day) as '当年第一天'

+------------+
| 当年第一天 |
+------------+
|2020-01-01 |
+------------+

获取下个月的第一天

select date_add(curdate()-day(now())+1,interval 1 month) as '下个月第一天'

+------------+
| 下个月第一天 |
+------------+
|2020-04-01 |
+------------+

字符串转日期函数–str_to_date()

select str_to_date('2020/02/23','%Y/%m/%d') ---2020-02-23
select str_to_date(concat('2020.','02.24'),'%Y.%m.%d')---2020-02-24
select str_to_date('08:09:30', '%h:%i:%s'); -- 08:09:30
select str_to_date('2020/02/23 09.23','%Y/%m/%d %h.%i.%s'); -- 2020-02-23 09:23:00

日期转字符串函数–date_format()

select date_format('2020-02-23 09:23:00', '%W %M %Y');

+------------------------------------------------+
| date_format('2020-02-23 09:23:00', '%W %M %Y') |
+------------------------------------------------+
| Sunday February 2020 |
+------------------------------------------------+

select date_format('2020-02-23 13:43:25','%Y%m%d%h%i%s')
+------------------------------------------------+
| date_format('2020-02-23 13:43:25','%Y%m%d%h%i%s') |
+------------------------------------------------+
| 20200223014325 |
+------------------------------------------------+