47、sql 日期函数往前推当前时间指定天数
1、mysql数据库
DATE_SUB(当前日期,往前推的天数)
SELECTdate_format(create_time,'%Y-%m-%d') as dateTime,sum( update_quantity ) AS tableCountFROMt_tableinfo_logWHEREcreate_time >= DATE_SUB( CURDATE( ), INTERVAL30DAY )AND create_time <= CURDATE( )GROUP BY dateTimeorder by dateTime
2、达梦数据库
ADD_DAYS(当前时间,往前天数)
SELECTADD_DAYS(create_time,0) as dateTime,sum( update_quantity ) AS tableCountFROMt_tableinfo_logWHEREcreate_time >= ADD_DAYS( CURDATE( ),-30)AND create_time <= CURDATE( )GROUP BY create_timeorder BY create_time
3、人大金仓
由于人大金仓没有相对应的函数,这里采用的是在业务中,把时间算出来,然后放进xml中
java中计算当前日期,往前推的天数
num 指的是往前的月份数
calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH)-num);
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");Date nowDate = new Date();Calendar calendar = Calendar.getInstance();calendar.setTime(nowDate);calendar.set(Calendar.MONTH, calendar.get(Calendar.MONTH)-num);Date updateDate2 = calendar.getTime();sdf.format(updateDate2);
xml如下所示:
<select id="getMonthData" resultType="com.module.tableInfo.vo.TableInfoVo">SELECTto_date(create_time,'YYYY-MM-DD') as dateTime,sum( update_quantity ) AS tableCountFROMt_tableinfo_logWHEREcreate_time >= #{startDate}AND create_time <= current_dateGROUP BY create_timeorder BY create_time</select>