mysql> select now(); +---------------------+ | now() | +---------------------+ | 2016-06-26 18:39:55 | +---------------------+ 1 row in set (0.17 sec) mysql> select curdate(); +------------+ | curdate() | +------------+ | 2016-06-26 | +------------+ 1 row in set (0.00 sec) mysql> select curtime(); +-----------+ | curtime() | +-----------+ | 18:40:14 | +-----------+ 1 row in set (0.00 sec) mysql> select extract(day from "2012-4-5"); +------------------------------+ | extract(day from "2012-4-5") | +------------------------------+ | 5 | +------------------------------+ 1 row in set (0.18 sec) mysql> select extract(year from "2012-4-5"); +-------------------------------+ | extract(year from "2012-4-5") | +-------------------------------+ | 2012 | +-------------------------------+ 1 row in set (0.00 sec) mysql> select extract(month from "2012-4-5"); +--------------------------------+ | extract(month from "2012-4-5") | +--------------------------------+ | 4 | +--------------------------------+ 1 row in set (0.00 sec)
mysql> select sysdate();
+---------------------+
| sysdate() |
+---------------------+
| 2016-06-26 18:49:15 |
+---------------------+
1 row in set (0.00 sec)
mysql> select date_add("2012-4-5",interval 1 month); +---------------------------------------+ | date_add("2012-4-5",interval 1 month) | +---------------------------------------+ | 2012-05-05 | +---------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add("2012-4-5",interval 1 year); +--------------------------------------+ | date_add("2012-4-5",interval 1 year) | +--------------------------------------+ | 2013-04-05 | +--------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add("2012-4-5",interval 1 day); +-------------------------------------+ | date_add("2012-4-5",interval 1 day) | +-------------------------------------+ | 2012-04-06 | +-------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add("2012-4-5",interval 1 minute); +----------------------------------------+ | date_add("2012-4-5",interval 1 minute) | +----------------------------------------+ | 2012-04-05 00:01:00 | +----------------------------------------+ 1 row in set (0.00 sec) mysql> select date_add("2012-4-5",interval 1 hour); +--------------------------------------+ | date_add("2012-4-5",interval 1 hour) | +--------------------------------------+ | 2012-04-05 01:00:00 | +--------------------------------------+ 1 row in set (0.00 sec)
mysql> select date_add("2012-4-5",interval 1 second);
+----------------------------------------+
| date_add("2012-4-5",interval 1 second) |
+----------------------------------------+
| 2012-04-05 00:00:01 |
+----------------------------------------+
1 row in set (0.00 sec)
mysql> select datediff("2010-7-21","2010-7-28"); +-----------------------------------+ | datediff("2010-7-21","2010-7-28") | +-----------------------------------+ | -7 | +-----------------------------------+ 1 row in set (0.01 sec)
date_add 函数参数形式 year month day hour minute second microscecond year_month day_hour day_minute day_second hour_minute hour_second minute_second second_microsecond
mysql> SELECT DATE_ADD('1997-12-31 23:59:59',INTERVAL '1.1' HOUR_MICROSECOND); +-----------------------------------------------------------------+ | DATE_ADD('1997-12-31 23:59:59',INTERVAL '1.1' HOUR_MICROSECOND) | +-----------------------------------------------------------------+ | 1998-01-01 00:00:00.100000 | +-----------------------------------------------------------------+ 1 row in set (0.00 sec)
mysql> select now(),year(now()); +---------------------+-------------+ | now() | year(now()) | +---------------------+-------------+ | 2016-06-26 20:07:15 | 2016 | +---------------------+-------------+ 1 row in set (0.00 sec) mysql> select now(),quarter(now()); +---------------------+----------------+ | now() | quarter(now()) | +---------------------+----------------+ | 2016-06-26 20:07:32 | 2 | +---------------------+----------------+ 1 row in set (0.00 sec) mysql> select now(),month(now()); +---------------------+--------------+ | now() | month(now()) | +---------------------+--------------+ | 2016-06-26 20:07:45 | 6 | +---------------------+--------------+ 1 row in set (0.00 sec) mysql> select now(),monthname(now()); +---------------------+------------------+ | now() | monthname(now()) | +---------------------+------------------+ | 2016-06-26 20:07:59 | June | +---------------------+------------------+ 1 row in set (0.00 sec)
mysql> select now(),dayofmonth(now()); +---------------------+-------------------+ | now() | dayofmonth(now()) | +---------------------+-------------------+ | 2016-06-26 20:08:24 | 26 | +---------------------+-------------------+ 1 row in set (0.00 sec) mysql> select now(),datename(now()); ERROR 1305 (42000): FUNCTION test.datename does not exist mysql> select now(),dayname(now()); +---------------------+----------------+ | now() | dayname(now()) | +---------------------+----------------+ | 2016-06-26 20:09:11 | Sunday | +---------------------+----------------+ 1 row in set (0.00 sec) mysql> select now(),dayofweek(now()); +---------------------+------------------+ | now() | dayofweek(now()) | +---------------------+------------------+ | 2016-06-26 20:09:49 | 1 | +---------------------+------------------+ 1 row in set (0.00 sec) mysql> select now(),hour(now()); +---------------------+-------------+ | now() | hour(now()) | +---------------------+-------------+ | 2016-06-26 20:10:08 | 20 | +---------------------+-------------+ 1 row in set (0.00 sec) mysql> select now(),minute(now()); +---------------------+---------------+ | now() | minute(now()) | +---------------------+---------------+ | 2016-06-26 20:10:15 | 10 | +---------------------+---------------+ 1 row in set (0.00 sec) mysql> select now(),second(now()); +---------------------+---------------+ | now() | second(now()) | +---------------------+---------------+ | 2016-06-26 20:10:39 | 39 | +---------------------+---------------+ 1 row in set (0.00 sec) mysql> select now(),microsecond(now()); +---------------------+--------------------+ | now() | microsecond(now()) | +---------------------+--------------------+ | 2016-06-26 20:11:19 | 0 | +---------------------+--------------------+ 1 row in set (0.00 sec)