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. expr
is 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.
unit Value |
Expected expr Format |
---|---|
MICROSECOND |
MICROSECONDS |
SECOND |
SECONDS |
MINUTE |
MINUTES |
HOUR |
HOURS |
DAY |
DAYS |
WEEK |
WEEKS |
MONTH |
MONTHS |
QUARTER |
QUARTERS |
YEAR |
YEARS |
SECOND_MICROSECOND |
'SECONDS.MICROSECONDS' |
MINUTE_MICROSECOND |
'MINUTES:SECONDS.MICROSECONDS' |
MINUTE_SECOND |
'MINUTES:SECONDS' |
HOUR_MICROSECOND |
'HOURS:MINUTES:SECONDS.MICROSECONDS' |
HOUR_SECOND |
'HOURS:MINUTES:SECONDS' |
HOUR_MINUTE |
'HOURS:MINUTES' |
DAY_MICROSECOND |
'DAYS HOURS:MINUTES:SECONDS.MICROSECONDS' |
DAY_SECOND |
'DAYS HOURS:MINUTES:SECONDS' |
DAY_MINUTE |
'DAYS HOURS:MINUTES' |
DAY_HOUR |
'DAYS HOURS' |
YEAR_MONTH |
'YEARS-MONTHS' |
以上是摘自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();
先到这里了,下班啦!
下一篇: 终究用什么框架