【Java】【MySQL】数据库存储的日期是字符类型，查询指定日期时间区间是用数据库的方法STR_TO

【Java】【MySQL】数据库存储的日期是字符类型，查询指定日期时间区间是用数据库的方法STR_TO_DATE().

2022-05-19 410

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

本文涉及的产品

RDS MySQL Serverless 基础系列，0.5-2RCU 50GB

云数据库 RDS MySQL，集群系列 2核4GB

RDS MySQL Serverless 高可用系列，价值2615元额度，1个月

简介： 【Java】【MySQL】数据库存储的日期是字符类型，查询指定日期时间区间是用数据库的方法STR_TO_DATE().

一、问题与分析

遇到的问题：找出xxx到期剩余x天的所有服务

场景限制条件：数据库表里存的是字符类型的日期格式（yyyy-MM-dd）

分析：当前需要解决的是表里的字段存储日期格式的类型是字符类型，最终想到的是利用STR_TO_DATE()的方法。（最后完美提高了几倍效率）

二、实现过程

一个表数据与表结构，如下图：

执行sql语句

SELECT * from t_doc_info WHERE 1 = 1 
and (STR_TO_DATE(remind_day,'%Y-%m-%d') 
BETWEEN STR_TO_DATE('2021-12-21','%Y-%m-%d') and STR_TO_DATE('2021-12-21','%Y-%m-%d'))
and is_del = 0

执行结果

微信截图_20220519194235.png

其中，STR_TO_DATE('2021-12-21','%Y-%m-%d')方法是把字符串类型的时间转化为时间格式，最后利用BETWEEN去求出剩余多少天的所有服务。

BETWEEN就是在这两个数之间的符合条件，没什么好说的。

运行java的代码（这里只是简单写核心的内容）

public static void main(String[] args) {
  Date date = new Date();
  SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
  String startDay = sdf.format(date);
  Calendar ca = Calendar.getInstance();
  ca.add(Calendar.DATE, 7); // 当前计算即将过期是剩余7天内（包括）
  date = ca.getTime();
  String endDay = sdf.format(date);
  System.err.println("即将过期，过期时间在：" + startDay + "和" + endDay + "之间");
}

运行结果（这里只是简单写核心的内容）

sql语句转成jdbc写法（这里只是简单写核心的内容）

import org.springframework.jdbc.core.JdbcTemplate;
public class TestDemoServiceImpl{
  @Autowired
  private JdbcTemplate jdbcTemplate;
  public List<DocBean> getExpireDocList(String startDay, String endDay,
      String userIdListStr) {
    StringBuffer sb = new StringBuffer();
    sb.append("SELECT * from t_doc_info");
    sb.append(" WHERE 1 = 1");
    if (StringUtils.isNotBlank(userIdListStr)) {
      sb.append(" AND user_id IN(" + userIdListStr + ")");
    }
    if (StringUtils.isNotBlank(startDay) && StringUtils.isNotBlank(endDay)) 
                { // 指定过期时间期间（格式为：yyyy-MM-dd）STR_TO_DATE()字符转为时间类型
      sb.append("AND (STR_TO_DATE(expire_day,'%Y-%m-%d') BETWEEN STR_TO_DATE('" + startDay + "','%Y-%m-%d') and STR_TO_DATE('" + endDay + "','%Y-%m-%d'))");
    }
    sb.append(" AND is_del = 0");
    try {
      return jdbcTemplate.query(sb.toString(),
          new BeanPropertyRowMapper<DocBean>(DocBean.class));
    } catch (DataAccessException e) {
      e.printStackTrace();
      return null;
    }
  }
}

扩展如果要添加条件，指定用户可以这样（例如：指定userId为1001、1002、1003的用户服务情况）

SELECT * from t_doc_info WHERE 1 = 1 
and (STR_TO_DATE(expire_day,'%Y-%m-%d') BETWEEN STR_TO_DATE('2021-12-21','%Y-%m-%d') and STR_TO_DATE('2021-12-21','%Y-%m-%d'))
and is_del = 0
and user_id in ('1001','1002','1003')