sql中的date的使用

前提：查询的时间范围为传入的参数

原sql语句传参date(a.send_time) >= :startDate AND date(a.send_time) <= :endDate

private static String smsSql = "SELECT count(1) FROM (SELECT 
a.phone FROM cl_sms a LEFT JOIN cl_user b ON a.phone = b.login_name
 LEFT JOIN cl_user_base_info c ON b.id = c.user_id" +  " WHERE 
 date(a.send_time) >= :startDate AND date(a.send_time) <= :endDate 
 GROUP BY a.phone ) d";

调用的sql语句sDate,eDate为传入的时间范围

MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("startDate", sDate);
    params.addValue("endDate", eDate);
List<Map<String, Object>>  list = firstJdbcTemplate.queryForList(sql,params);

不使用date时,传入的参数的时分秒被截取掉了2018-03-01 00:00:00--2018-04-01 00:00:00

SELECT count(1) FROM (SELECT a.phone FROM cl_sms a LEFT JOIN cl_user b
 ON a.phone = b.login_name LEFT JOIN cl_user_base_info c ON b.id = 
 c.user_id WHERE a.send_time >= '2018-03-01'AND 
 a.send_time <= '2018-04-01'AND  OR c.real_name NOT IN 
 ('A Sunu Widyatmoko','jarry','king' ) )GROUP BY a.phone) d;

不使用date时,传入时间参数带着时分秒2018-03-01 00:00:00--2018-04-01 23:59:59

SELECT count(1) FROM (SELECT a.phone FROM cl_sms a LEFT JOIN cl_user b
 ON a.phone = b.login_name LEFT JOIN cl_user_base_info c ON b.id = 
 c.user_id WHERE a.send_time >= '2018-03-01 00:00:00'AND 
 a.send_time <= '2018-04-01 23:59:59'AND  OR c.real_name NOT IN 
 ('A Sunu Widyatmoko','jarry','king' ) )GROUP BY a.phone) d;

使用date时,传入的参数的时分秒被截取掉了2018-03-01 00:00:00--2018-04-01 23:59:59

SELECT count(1) FROM (SELECT a.phone FROM cl_sms a LEFT JOIN cl_user b
 ON a.phone = b.login_name LEFT JOIN cl_user_base_info c ON b.id = 
 c.user_id WHERE date(a.send_time) >= '2018-03-01'AND 
 date(a.send_time) <= '2018-04-01'AND  OR c.real_name NOT IN 
 ('A Sunu Widyatmoko','jarry','king' ) )GROUP BY a.phone) d;

针对有关时间范围的查询还是需要谨慎，否则间隔一天时间