mysql日期与时间函数代码实例

发布时间:2020-05-27编辑:脚本学堂
mysql日期与时间函数的用法,NOW、CURRENT_TIMESTAMP和SYSDATE日期时间函数、时间回头函数,以及mysql DATE_FORMAT函数格式化日期的例子。

mysql日期与时间函数用法

1、NOW、CURRENT_TIMESTAMP和SYSDATE
这些函数都能返回当前的系统时间,它们之间有区别吗?先来看个例子。
 

复制代码 代码示例:
mysql> SELECT NOW(),CURRENT_TIMESTAMP(),SYSDATE()G;
*************************** 1. row ***************************
NOW(): 2011-02-04 20:35:04
CURRENT_TIMESTAMP(): 2011-02-04 20:35:04
SYSDATE(): 2011-02-04 20:35:04
 

以上例子中,3个函数都是返回当前的系统时间。

例子:
 

复制代码 代码示例:
mysql>SELECT
->     NOW(),
->     CURRENT_TIMESTAMP(),
->     SYSDATE(),
->     SLEEP(2),
->     NOW(),
->     CURRENT_TIMESTAMP(),
->     SYSDATE()G
*************************** 1. row ***************************
NOW(): 2011-11-29 21:04:49
CURRENT_TIMESTAMP(): 2011-11-29 21:04:49
SYSDATE(): 2011-11-29 21:04:49
SLEEP(2): 0
NOW(): 2011-11-29 21:04:49
CURRENT_TIMESTAMP(): 2011-11-29 21:04:49
SYSDATE(): 2011-11-29 21:04:51
1 row in set (2.00 sec)

以上例子中人为地加入了SLEEP函数,让其等待2秒,SYSDATE返回的时间和NOW及CURRENT_TIMESTAMP是不同的,SYSDATE函数慢了2秒。

究其原因是这3个函数有略微区别:
1、CURRENT_TIMESTAMP是NOW的同义词,也就是说两者是相同的。
2、SYSDATE函数返回的是执行到当前函数时的时间,而NOW返回的是执行sql语句时的时间。

因此,以上例子中,两次执行SYSDATE函数返回不同的时间是因为第二次调用执行该函数时等待了前面SLEEP函数2秒。
而对于NOW函数,不管是在SLEEP函数之前还是之后执行,返回的都是执行这条SQL语句时的时间。

2、时间加减函数 mysql日期加减

例子:
 

复制代码 代码示例:
mysql> SELECT NOW(),NOW()+0G;
*************************** 1. row ***************************
NOW(): 2011-02-04 20:46:33
NOW()+0: 20110204204633.000000
1 row in set (0.00 sec)
 

可以看到,NOW()函数可以返回时间,也可以返回一个数字,就看用户如何使用。
如果相对当前时间进行增加或减少,并不能直接加上或减去一个数字,而需要使用特定的函数,如DATE_ADD或DATE_SUB,前者表示增加,后者表示减少。

具体使用方法有DATE_ADD(date,INTERVAL expr unit)和 DATE_SUB(date,INTERVAL expr unit),示例如下:
 

复制代码 代码示例:
mysql> SELECT NOW() AS now,
DATE_ADD(now(),INTERVAL 1 DAY) AS tomorrow,
DATE_SUB(now(),INTERVAL 1 DAY) AS yesterdayG;
*************************** 1. row ***************************
now: 2011-02-04 20:53:25
tomorrow: 2011-02-05 20:53:25
yesterday: 2011-02-03 20:53:25
1 row in set (0.00 sec)

其中expr值可以是正值也可以是负值,因此可以使用DATE_ADD函数来完成DATE_SUB函数的工作,例如:
 

复制代码 代码示例:
mysql> SELECT NOW() AS now,
DATE_ADD(NOW(),INTERVAL 1 DAY) AS tomorrow,
DATE_ADD(NOW(),INTERVAL -1 DAY) AS yesterdayG;
*************************** 1. row ***************************
now: 2011-02-04 20:55:40
tomorrow: 2011-02-05 20:55:40
yesterday: 2011-02-03 20:55:40
1 row in set (0.00 sec)

问题,如果是闰月,那么DATE_ADD函数怎么处理呢?MySQL的默认行为是这样的:
如果目标年份是闰月,那么返回的日期为2月29日;如果不是闰月,那么返回日期是2月28日。

代码:
 

复制代码 代码示例:
mysql> SELECT DATE_ADD(’2000-2-29′,INTERVAL 4 YEAR) AS year;
+————+
| year        |
+————+
| 2004-02-29 |
+————+
1 row in set (0.00 sec)
 
mysql> SELECT DATE_ADD(’2000-2-29′,INTERVAL 1 YEAR) AS year;
+————+
| year        |
+————+
| 2001-02-28 |
+————+
1 row in set (0.00 sec)

以上例子中使用了DAY和YEAR数据类型,其实也可以使用MICROSECOND、SECOND、MINUTE、HOUR、WEEK、MONTH等类型,例如:
 

复制代码 代码示例:
mysql> SELECT NOW() AS now,
DATE_ADD(NOW(), INTERVAL 1 HOUR) AS next timeG;
*************************** 1. row ***************************
now: 2011-02-04 21:00:15
next time: 2011-02-04 22:00:15
1 row in set (0.00 sec)

3. DATE_FORMAT函数


这个函数本身没有什么需要探讨的地方,其作用只是按照用户的需求格式化打印出日期,例如:
 

复制代码 代码示例:
mysql> SELECT DATE_FORMAT(NOW(),’%Y%m%d’) AS datetime;
+———-+
| datetime |
+———-+
| 20110204 |
+———-+
1 row in set (0.00 sec)
 

但是开发人员往往会错误地使用这个函数,导致非常严重的后果。

例如,在需要查询某一天的数据时,语句:
 

复制代码 代码示例:
SELECT * FROM table WHERE DATE_FORMAT(date,’%Y%m%d’)=’xxxx-xx-xx’;

一般来说表中都会有一个对日期类型的索引,如果使用上述的语句,优化器绝对不会使用索引,也不可能通过索引来查询数据,因此上述查询的执行效率可能非常低。