1、显示当前日期
mysql> select curdate();
+------------+
| curdate() |
+------------+
| 2016-12-28 |
+------------+
1 row in set (0.01 sec)
2、显示当前时间
mysql> select curtime();
+-----------+
| curtime() |
+-----------+
| 19:00:30 |
+-----------+
1 row in set (0.00 sec)
3、显示当前日期和时间
mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2016-12-28 19:05:59 |
+---------------------+
1 row in set (0.00 sec)
4、将时间转换为时间戳
mysql> select unix_timestamp('2016-12-28 19:05:59');
+---------------------------------------+
| unix_timestamp('2016-12-28 19:05:59') |
+---------------------------------------+
| 1482969959 |
+---------------------------------------+
1 row in set (0.00 sec)
5、时间戳转换为日期
mysql> select from_unixtime(1482969959);
+---------------------------+
| from_unixtime(1482969959) |
+---------------------------+
| 2016-12-28 19:05:59 |
+---------------------------+
1 row in set (0.00 sec)
6、显示日期是当年的第几周
mysql> select week('2016-12-28 19:05:59');
+-----------------------------+
| week('2016-12-28 19:05:59') |
+-----------------------------+
| 52 |
+-----------------------------+
1 row in set (0.00 sec)
(能支持日期或者日期+时间)
7、返回日期的年份
mysql> select year('2016-12-28 19:05:59');
+-----------------------------+
| year('2016-12-28 19:05:59') |
+-----------------------------+
| 2016 |
+-----------------------------+
1 row in set (0.00 sec)
(能支持日期或者日期+时间)
8、返回小时值
mysql> select hour('2016-12-28 19:05:59');
+-----------------------------+
| hour('2016-12-28 19:05:59') |
+-----------------------------+
| 19 |
+-----------------------------+
1 row in set (0.00 sec)
9、返回分钟
mysql> select minute('2016-12-28 19:05:59');
+-------------------------------+
| minute('2016-12-28 19:05:59') |
+-------------------------------+
| 5 |
+-------------------------------+
1 row in set (0.00 sec)
10、返回月份
mysql> select monthname('2016-12-28 19:05:59');
+----------------------------------+
| monthname('2016-12-28 19:05:59') |
+----------------------------------+
| December |
+----------------------------------+
1 row in set (0.00 sec)
11、按照指定格式显示时间
mysql> select date_format(now(),'%Y-%M-%D');
+-------------------------------+
| date_format(now(),'%Y-%M-%D') |
+-------------------------------+
| 2016-December-28th |
+-------------------------------+
1 row in set (0.00 sec)
12、计算两个时间相差的天数(前-后)
mysql> select datediff('2016-10-01',now());
+------------------------------+
| datediff('2016-10-01',now()) |
+------------------------------+
| -88 |
+------------------------------+
1 row in set (0.00 sec)
13、返回31天后的时间、返回一年两个月后的时间
mysql> select now() current,date_add(now(),interval 31 day) after31days,date_add(now(),interval '1_2' year_month) after_oneyear_twomonth;
+---------------------+---------------------+------------------------+
| current | after31days | after_oneyear_twomonth |
+---------------------+---------------------+------------------------+
| 2016-12-28 19:12:33 | 2017-01-28 19:12:33 | 2018-02-28 19:12:33 |
+---------------------+---------------------+------------------------+
1 row in set (0.00 sec)