SQL實現查找最近一周、一個月、三個月的數據_MySQL

首先來看看MySQL的DATE_ADD函數：

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官網的文檔，鏈接：http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-add

用法如下：

當前時間：

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

下一個月：
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)

上一個月：
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>

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

最終的SQL語句如下：

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

先到這里了，下班啦！

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com