Mysql日期和时间函数总结

(一)使用日期函数获取当前日期

日期格式：YYYY-MM-DDmysql> select curdate(),current_date(),curdate()+0;+------------+----------------+-------------+| curdate()  | current_date() | curdate()+0 |+------------+----------------+-------------+| 2017-09-01 | 2017-09-01     |    20170901 |+------------+----------------+-------------+1 row in set

---

(二)使用时间函数获取当前时间

时间格式：HH:MM:SSmysql> select curtime(),current_time(),curtime()+0;+-----------+----------------+-------------+| curtime() | current_time() | curtime()+0 |+-----------+----------------+-------------+| 16:09:42  | 16:09:42       |      160942 |+-----------+----------------+-------------+1 row in set

---

(三)使用日期时间函数获取当前系统日期时间

日期时间格式：mysql> select current_timestamp(),localtime(),now(),sysdate();+---------------------+---------------------+---------------------+---------------------+| current_timestamp() | localtime()         | now()               | sysdate()           |+---------------------+---------------------+---------------------+---------------------+| 2017-09-01 16:12:43 | 2017-09-01 16:12:43 | 2017-09-01 16:12:43 | 2017-09-01 16:12:43 |+---------------------+---------------------+---------------------+---------------------+1 row in set

---

(四)使用UTC_DATE()函数获取日期 UTF(世界标准时间)

mysql> select utc_date(),utc_date()+0;+------------+--------------+| utc_date() | utc_date()+0 |+------------+--------------+| 2017-09-01 |     20170901 |+------------+--------------+

---

(五)使用UTC_TIME()获取当前时间

mysql> select utc_time(),utc_time()+0;+------------+--------------+| utc_time() | utc_time()+0 |+------------+--------------+| 08:19:36   |        81936 |+------------+--------------+1 row in set

---

(六)使用month(date)、monthname(data)获取月份

#1以数字的形式返回月份mysql> select month(curdate())as Month;+-------+| Month |+-------+|     9 |+-------+1 row in set#2以英语字符串返回月份mysql> select monthname(now()) as Month;+-----------+| Month     |+-----------+| September |+-----------+1 row in set

---

(七)使用dayname(date)、dayofweek(date)、weekday(date)获取星期

mysql> select dayname(curdate()),dayofweek(now()),weekday(current_date());+--------------------+------------------+-------------------------+| dayname(curdate()) | dayofweek(now()) | weekday(current_date()) |+--------------------+------------------+-------------------------+| Friday             |                6 |                       4 |+--------------------+------------------+-------------------------+1 row in set

(八)使用dayofyear(date)和dayofmonth(date),表示已经过了多少天

mysql> select dayofyear(now()),dayofmonth(now());+------------------+-------------------+| dayofyear(now()) | dayofmonth(now()) |+------------------+-------------------+|              244 |                 1 |+------------------+-------------------+1 row in set

---

(九)获取年份、季度、分钟、秒钟函数的使用

1.获取年份mysql>  select now(),year(now())as Year;+---------------------+------+| now()               | Year |+---------------------+------+| 2017-09-01 16:42:26 | 2017 |+---------------------+------+1 row in set1 row in set2.获取季度mysql> select curdate(),quarter(now()) as Quarter;+------------+---------+| curdate()  | Quarter |+------------+---------+| 2017-09-01 |       3 |+------------+---------+1 row in set3.获取分钟mysql> select curtime(),minute(curtime()) as Minute;+-----------+--------+| curtime() | Minute |+-----------+--------+| 16:43:43  |     43 |+-----------+--------+1 row in set4.获取秒钟mysql>  select curtime()as Referenced ,second(curtime()) as Second;+------------+--------+| Referenced | Second |+------------+--------+| 16:41:51   |     51 |+------------+--------+1 row in set

---

(十) 时间和秒钟转换函数

转换公式：H*3600+M*60+S;#1将时间转换为秒mysql>  select curtime(),time_to_sec(curtime());+-----------+------------------------+| curtime() | time_to_sec(curtime()) |+-----------+------------------------+| 16:46:55  |                  60415 |+-----------+------------------------+1 row in set

---

(十一)使用格式化日期函数操作日期和函数

mysql> select now(),date_format(now(),'%Y-%m-%d')as AfterFormate ,date_format(now(),'%H:%i:%s');+---------------------+--------------+-------------------------------+| now()               | AfterFormate | date_format(now(),'%H:%i:%s') |+---------------------+--------------+-------------------------------+| 2017-09-01 17:02:34 | 2017-09-01   | 17:02:34                      |+---------------------+--------------+-------------------------------+1 row in set