开发中mysql常用函数

简单写写后面用到了再来补，欢迎补充。

-- 1.获取当前日期时间
select now(); -- 2020-08-11 17:36:38
-- 2.获取当前日期
select curdate(); -- 2020-08-11
select date(now()); -- 2020-08-11
-- 3.获取时间
select curtime(); -- 17:37:04
select time(now()); -- 17:37:04
-- 4.获取年月日时分秒
select year(now()),month(now()),day(now()),hour(now()),minute(now()),second(now()); -- 2020,8,11,17,37,14
-- 5.获取当前日期所在的周数、周几
select weekofyear(now()); -- 33
select dayofweek(now()); -- 3(默认是周日第一天)
-- 6.extract函数获取年月日时分秒周
select
     extract(year from now())  as year      -- 2020
    ,extract(month from now()) as month     -- 8
    ,extract(day from now())   as day       -- 11
    ,extract(hour from now())  as hour      -- 17
    ,extract(minute from now()) as minute   -- 38
    ,extract(second from now()) as second   -- 1
    ,extract(week from now()) as week;      -- 32
    -- 2020,8,11,17,38,1,32


-- 7.日期计算相关函数(date_add,)
select
    now() as cur_time                                   -- 2020-08-11 17:38:20
    ,date_add(now(),interval 7 day) as after_7_day      -- 2020-08-18 17:38:20
    ,date_add(now(),interval 7 month) as after_7_month  -- 2021-03-11 17:38:20
    ,date_add(now(),interval 7 year) as after_7_year;   -- 2027-08-11 17:38:20

select
    now() as cur_time                                   -- 2020-08-11 17:40:27
    ,date_sub(now(),interval 7 day) as before_7_day     -- 2020-08-04 17:40:27
    ,date_sub(now(),interval 7 month) as before_7_month -- 2020-01-11 17:40:27
    ,date_sub(now(),interval 7 year) as before_7_year;  -- 2013-08-11 17:40:27

select datediff(now(),'2014-09-10');    -- 2162

-- 8.时间格式转换
select date_format(now(),'%Y-%m-%d');  -- 2020-08-11
select date_format(now(),'%H:%i:%S');  -- 17:42:38
select str_to_date('2020-08-11 17:35:55','%Y-%m-%d %H:%i:%s'); -- 2020-08-11 17:35:55