sql 日期函数往前推当前时间指定天数

47、sql 日期函数往前推当前时间指定天数

1、mysql数据库

DATE_SUB(当前日期，往前推的天数)

   SELECT
        date_format(create_time,'%Y-%m-%d') as dateTime,
        sum( update_quantity ) AS tableCount
        FROM
        t_tableinfo_log
        WHERE
        create_time >= DATE_SUB( CURDATE( ), INTERVAL
        30
        DAY )
        AND create_time <=  CURDATE( )
        GROUP BY dateTime
        order by dateTime

2、达梦数据库

ADD_DAYS(当前时间，往前天数)

SELECT
ADD_DAYS(create_time,0) as dateTime,
sum( update_quantity ) AS tableCount
FROM
t_tableinfo_log
WHERE
create_time >= ADD_DAYS( CURDATE( ),
-30
)
AND create_time <=  CURDATE( )
GROUP BY create_time
order 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">
        SELECT
        to_date(create_time,'YYYY-MM-DD') as dateTime,
        sum( update_quantity ) AS tableCount
        FROM
        t_tableinfo_log
        WHERE
        create_time &gt;= #{startDate}
        AND create_time &lt;=  current_date
        GROUP BY create_time
        order BY create_time
    </select>