本文给大家带来MySQL时间和日期函数的介绍，下面是MySql各种内置时间函数：
1. curdate() , current_date() 用于获取当前的日期。
2. curtime() , current_time() 用于获取现在的时钟时间。
3. now()，current_timestamp()，localtime()，sysdate() 四类函数可以获取当前的日期和时钟时间
4. datediff()用于获取两个日期相隔的天数：datediff('2018.08.20','2019.02.03') /* 得到的结果是有符号的。*/
5. adddate('n',d): 求得日期是n经过d天后的新日期。
6. adddate('n',interval 'a' type) 函数返回日期n后经过时间段a后的新时间。
7. subdate ('n',d) 日期n前d天。 8. unix_timestamp() 返回当前时间的 unix时间戳 unix_timestamp('n') 返回时间是n的 unix时间戳 # Unix时间戳（Unix timestamp） 是从1970年1月1日（UTC/GMT的午夜）开始所经过的秒数，不考虑闰秒。 9. monthname(n),dayname(n) 获取日期的月份和星期名
10. dayofyear(n),weekofyear(n),dayofmonth(n); 获取日期的天数，星期数、详见实践。
11. hour(n) | minute(n) | second(n) 获取时间的时分秒。
12. extract(type from n) 从时间n提取type信息
13. time_to_sec(n) 将n转化成秒为单位的时间 sec_to_time(n) 将单位的时间转化成时分秒的表达式
14. date_format(d,f) 将日期d转化成f格式 time_format(t,f) 将时间t转化成f格式
C:\Users\Administrator>mysql -u root -p
Enter password: ********
1.
mysql> select curdate(),current_date();
+------------+----------------+
| curdate() | current_date() |
+------------+----------------+
| 2016-02-03 | 2016-02-03 |
+------------+----------------+
1 row in set (0.01 sec)
2.
mysql> select curtime(),current_time();
+-----------+----------------+
| curtime() | current_time() |
+-----------+----------------+
| 21:36:39 | 21:36:39 |
+-----------+----------------+
1 row in set (0.00 sec)
3.
mysql> select now(),current_timestamp(),localtime(),sysdate();
+---------------------+---------------------+---------------------+---------------------+
| now() | current_timestamp() | localtime() | sysdate() |
+---------------------+---------------------+---------------------+---------------------+
| 2016-02-03 21:40:50 | 2016-02-03 21:40:50 | 2016-02-03 21:40:50 | 2016-02-03 21:40:50 |
+---------------------+---------------------+---------------------+---------------------+
1 row in set (0.00 sec)
4.
mysql> select datediff('2015.08.20','2016.02.03');
+-------------------------------------+
| datediff('2015.08.20','2016.02.03') |
+-------------------------------------+
| -167 |
+-------------------------------------+
1 row in set (0.02 sec)
5.
mysql> select adddate('2019.02.03','10');
+----------------------------+
| adddate('2016.02.03','10') |
+----------------------------+
| 2016-02-13 |
+----------------------------+
1 row in set (0.03 sec)
# 不过他们的格式没有严格的要求：
mysql> select adddate('2019-02-03',10);
+--------------------------+
| adddate('2016-02-03',10) |
+--------------------------+
| 2016-02-13 |
+--------------------------+
1 row in set (0.00 sec)
6.
mysql> select adddate('2019-02-03',interval '12 3' year_month);
+--------------------------------------------------+
| adddate('2019-02-03',interval '12 3' year_month) |
+--------------------------------------------------+
| 2028-05-03 |
+--------------------------------------------------+
1 row in set (0.00 sec)
7.
mysql> select subdate('2016-02-03',10);
+--------------------------+
| subdate('2016-02-03',10) |
+--------------------------+
| 2016-01-24 |
+--------------------------+
1 row in set (0.00 sec)
8.
mysql> select unix_timestamp(),unix_timestamp('2016.02.03');
+------------------+------------------------------+
| unix_timestamp() | unix_timestamp('2016.02.03') |
+------------------+------------------------------+
| 1454509344 | 1454428800 |
+------------------+------------------------------+
1 row in set (0.00 sec)
9.
mysql> select monthname(curdate()),dayname(curdate());
+----------------------+--------------------+
| monthname(curdate()) | dayname(curdate()) |
+----------------------+--------------------+
| February | Wednesday |
+----------------------+--------------------+
1 row in set (0.04 sec)
10.
mysql> select dayofyear(curdate()),weekofyear(curdate()),dayofmonth(curdate());
+----------------------+-----------------------+-----------------------+
| dayofyear(curdate()) | weekofyear(curdate()) | dayofmonth(curdate()) |
+----------------------+-----------------------+-----------------------+
| 34 | 5 | 3 |
+----------------------+-----------------------+-----------------------+
1 row in set (0.00 sec)
11.
mysql> select hour(now()),minute(now()),second(now());
+-------------+---------------+---------------+
| hour(now()) | minute(now()) | second(now()) |
+-------------+---------------+---------------+
| 22 | 36 | 29 |
+-------------+---------------+---------------+
1 row in set (0.03 sec)
12.
mysql> select extract(hour from now()),extract(month from now()),extract(year from now());
+--------------------------+---------------------------+--------------------------+
| extract(hour from now()) | extract(month from now()) | extract(year from now()) |
+--------------------------+---------------------------+--------------------------+
| 22 | 2 | 2016 |
+--------------------------+---------------------------+--------------------------+
1 row in set (0.00 sec)
13.
mysql> select time_to_sec(now()),sec_to_time('10000');
+--------------------+----------------------+
| time_to_sec(now()) | sec_to_time('10000') |
+--------------------+----------------------+
| 81870 | 02:46:40 |
+--------------------+----------------------+
1 row in set (0.00 sec)
14.
mysql> select date_format(curdate(),'year:%y month:%m day:%d');
+-------------------------+
| date |
+-------------------------+
| year:16 month:02 day:03 |
+-------------------------+
1 row in set (0.00 sec)
mysql> select date_format(now(),'%T');
+----------+
| time |
+----------+
| 22:57:02 |
+----------+
1 row in set (0.00 sec)
mysql> select date_format(now(),'%y.%m.%d %T');
+----------------------------------+
| date_format(now(),'%y.%m.%d %T') |
+----------------------------------+
| 16.02.03 23:02:15 |
+----------------------------------+
1 row in set (0.00 sec)
mysql> select time_format(now(),'date:%y-%m-%d time:%h:%m:%s');
+--------------------------------------------------+
| time_format(now(),'date:%y-%m-%d time:%h:%m:%s') |
+--------------------------------------------------+
| date:00-00-00 time:11:00:33 |
+--------------------------------------------------+
1 row in set (0.00 sec)