|
该函数计算从默认日期和时间(0000年1月1日)开始经历number天后的日期和时间
datediff(date1,date2)
计算date1和date2之间相隔天数
mysql> select now() as 'now',to_days(now()) as 'toDays',from_days(to_days(now())+7) as 'from_days',datediff(now(),'2000-12-01') 'datediff';
+---------------------+--------+------------+----------+
| now | toDays | from_days | datediff |
+---------------------+--------+------------+----------+
| 2015-04-30 08:27:11 | 736083 | 2015-05-07 | 5263 |
+---------------------+--------+------------+----------+
2、计算与指定日期和时间相互操作的函数
adddate(date,n)
subdate(date,n)
mysql> select curdate() as 'curdate',adddate(curdate(),5) as 'five day after',subdate(curdate(),5) as 'five day before';
+------------+----------------+-----------------+
| curdate | five day after | five day before |
+------------+----------------+-----------------+
| 2015-04-30 | 2015-05-05 | 2015-04-25 |
+------------+----------------+-----------------+
adddate(d,interval expr type)
subdate(d,interval expr type)
interval:间隔，区间
type的值
SECOND 秒 ss
MINUTE 分钟 mm
HOUR 小时 hh
DAY 日 DD
MONTH 月 MM
YEAR 年 YY
MINUTE_SECOND 分钟和秒 mm和ss之间用任意符号隔开
HOUR_SECOND 小时和秒 hh和ss之间用任意符号隔开
HOUR_MINUTE
DAY_SECOND
DAY_MINUTE
DAY_HOUR
YEAR_MONTH
mysql> select curdate() as 'curdate',adddate(curdate(),interval '2,3' year_month) as 'two year three month after';
+------------+----------------------------+
| curdate | two year three month after |
+------------+----------------------------+
| 2015-04-30 | 2017-07-30 |
+------------+----------------------------+
1 row in set (0.00 sec)
mysql> select curdate() as 'curdate',adddate(curdate(),interval '2,3' day_minute) as 'two year three month after';
+------------+----------------------------+
| curdate | two year three month after |
+------------+----------------------------+
| 2015-04-30 | 2015-04-30 02:03:00 |
+------------+----------------------------+
注意：第二个查询语句没有出现想要的结果
addtime()
subtime()
mysql> select curtime() as 'curtime',addtime(curtime(),5) as 'five second after',subtime(curtime(),5) as 'five second before';
+----------+-------------------+--------------------+
| curtime | five second after | five second before |
+----------+-------------------+--------------------+
| 08:42:08 | 08:42:13 | 08:42:03 |
+----------+-------------------+--------------------+
1 row in set (0.01 sec)
mysql> select curtime() as 'curtime',addtime(curtime(),5*60) as 'five minute after',subtime(curtime(),5*60) as 'five minute before';
+----------+-------------------+--------------------+
| curtime | five minute after | five minute before |
+----------+-------------------+--------------------+
| 08:42:54 | 08:45:54 | 08:39:54 |
+----------+-------------------+--------------------+
系统信息函数
version()
database()
user()
last_insert_id()
mysql> select version() as 'version',database() as 'database',user() as 'user',last_insert_id() as 'auto_increment_id'
+--------------------------+----------+----------------+-------------------+
| version | database | user | auto_increment_id |
+--------------------------+----------+----------------+-------------------+
| 5.0.51b-community-nt-log | test3 | root@localhost | 0 |
+--------------------------+----------+----------------+-------------------+
|