SQL實(shí)現(xiàn)查找最近一周、一個(gè)月、三個(gè)月的數(shù)據(jù)_MySQL

首先來看看MySQL的DATE_ADD函數(shù)：

DATE_ADD(date,INTERVAL expr unit), DATE_SUB(date,INTERVAL expr unit)

These functions perform date arithmetic. The date argument specifies the starting date or datetime value. expris an expression specifying the interval value to be added or subtracted from the starting date. expr is a string; it may start with a “-” for negative intervals. unit is a keyword indicating the units in which the expression should be interpreted.

The INTERVAL keyword and the unit specifier are not case sensitive.

The following table shows the expected form of the expr argument for each unit value.

以上是摘自MySQL官網(wǎng)的文檔，鏈接：http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

用法如下：

當(dāng)前時(shí)間：

mysql> select now();
+---------------------+
| now() |
+---------------------+
| 2014-07-03 21:16:22 |
+---------------------+
1 row in set (0.00 sec)

下一個(gè)月：
mysql> select date_add(now(),interval 1 month);
+----------------------------------+
| date_add(now(),interval 1 month) |
+----------------------------------+
| 2014-08-03 21:16:28 |
+----------------------------------+
1 row in set (0.00 sec)

上一個(gè)月：
mysql> select date_add(now(),interval -1 month);
+-----------------------------------+
| date_add(now(),interval -1 month) |
+-----------------------------------+
| 2014-06-03 21:17:02 |
+-----------------------------------+
1 row in set (0.00 sec)

mysql>

項(xiàng)目中order表中的createTime是unix timestamp的，所以首先需要轉(zhuǎn)換一下日期格式，這里用到了 FROM_UNIXTIME(timestamp)。

最終的SQL語句如下：

select from_unixtime(createTime) as datetime from wm_order having datetime between date_add(now(),interval -1 month) and now();

先到這里了，下班啦！

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com