日期时间函数

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)