1.sql语句中时间的叠加或相减遇到月末的bug
正确的查询：
select date_add(CURDATE(), interval (SELECT VALUE FROM
code_config
WHERE
CODE = 'zcbArrangeAfterDay'
LIMIT 1
) day)
2019-03-07
select DATE_SUB(CURDATE(), interval (SELECT VALUE FROM
code_config
WHERE
CODE = 'zcbArrangeBeforeDay'
LIMIT 1
) day)
2019-02-25
SELECT CURDATE()
2019-02-28
错误的查询方式：
SELECT CURDATE() -(SELECT VALUE FROM code_config WHERE CODE='zcbArrangeBeforeDay' limit 1)
20190225
SELECT CURDATE() +(SELECT VALUE FROM code_config WHERE CODE='zcbArrangeAfterDay' limit 1)
20190235
原因，每当月末时，时间可能叠加超过本月时间总天数
2.通过sql二位数组时间排期举例说明：
表结构：
CREATE TABLE `project_scheduling` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`corp_id` bigint(20) DEFAULT NULL COMMENT '企业ID',
`demand_id` bigint(20) DEFAULT NULL COMMENT '需求ID',
`project_id` bigint(20) DEFAULT NULL COMMENT '项目ID',
`scheduling_type` tinyint(4) DEFAULT NULL COMMENT '排期类型\n 1：全天\n 2：凌晨\n 3：上午\n 4：下午\n 5：晚上',
`scheduling_date` date DEFAULT NULL COMMENT '日期',
`start_time` datetime DEFAULT NULL COMMENT '开始时间',
`end_time` datetime DEFAULT NULL COMMENT '结束时间',
`status_no` tinyint(4) DEFAULT NULL COMMENT '状态(1:可用，2：不可用)',
`recruit_num` int(11) DEFAULT NULL COMMENT '招聘人数(冗余)',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`source` tinyint(4) DEFAULT NULL COMMENT '1：系统自动排期 2/null 手动排期',
`interviewcycle` varchar(50) DEFAULT NULL COMMENT '面试周期',
PRIMARY KEY (`id`),
KEY `IDX_PROJECT_SCHEDULING_DEMAND_ID` (`demand_id`),
KEY `IDX_PROJECT_SCHEDULING_STATUS_NO` (`status_no`),
KEY `IDX_PROJECT_SCHEDULING_END_TIME` (`end_time`),
KEY `IDX_PROJECT_ID` (`project_id`) USING BTREE,
KEY `idx_statusno_endtime` (`status_no`,`end_time`),
KEY `idx_endtime_statusno_demandid_projectid` (`end_time`,`status_no`,`demand_id`,`project_id`)
) ENGINE=InnoDB AUTO_INCREMENT=5463509 DEFAULT CHARSET=utf8 COMMENT='订单需求排期表';
service层代码：
主要dao层的sql最重要：
public ResultMessage getProjectScheduling(Map<String, Object> searchParams) {
List<PositionSchedulingVo> projectScheduling = null;
try {
projectScheduling = projectSchedulingDao.getProjectScheduling(searchParams);
if (projectScheduling != null && projectScheduling.size() > 0) {
return new ResultMessage(SysCode.SUCCESS.getCode(), SysCode.SUCCESS.getDesc(), projectScheduling);
}
return new ResultMessage(SysCode.SUCCESS.getCode(), "该职位在近7天里没有相应的排期", projectScheduling);
} catch (Exception e) {
e.printStackTrace();
logger.error("获取职位排期失败 getProjectScheduling 职位ID" + searchParams);
return new ResultMessage(SysCode.FAILUE.getCode(), "获取职位排期异常");
}
}
具体的sql：
<!-- 招财宝根据demandId获取近期可面试时间，是否有排期”条件扩展至“前（配置天数）天+未来7天”内是否有排期-->
<select id="getProjectScheduling" resultMap="RM_PositionSchedulingVo">
SELECT
scheduling_date schedulingDate,
max(case when scheduling_type = 2 then id end) id0,
max(case when scheduling_type = 2 then scheduling_type end) type0,
max(case when scheduling_type = 2 then start_time end) st0,
max(case when scheduling_type = 2 then end_time end) et0,
max(case when scheduling_type = 3 then id end) id1,
max(case when scheduling_type = 3 then scheduling_type end) type1,
max(case when scheduling_type = 3 then start_time end) st1,
max(case when scheduling_type = 3 then end_time end) et1,
max(case when scheduling_type = 4 then id end) id2,
max(case when scheduling_type = 4 then scheduling_type end) type2,
max(case when scheduling_type = 4 then start_time end) st2,
max(case when scheduling_type = 4 then end_time end) et2,
max(case when scheduling_type = 5 then id end) id3,
max(case when scheduling_type = 5 then scheduling_type end) type3,
max(case when scheduling_type = 5 then start_time end) st3,
max(case when scheduling_type = 5 then end_time end) et3
FROM
project_scheduling
WHERE
status_no = 1
AND demand_id = #{demandId}
AND scheduling_date >= DATE_SUB(curdate(),INTERVAL
(SELECT VALUE FROM code_config WHERE CODE='hzArrangeBeforeDay' limit 1) DAY)
AND scheduling_date <= DATE_add(curdate(),INTERVAL (SELECT VALUE FROM code_config WHERE CODE='hzArrangeAfterDay' limit 1) DAY)
group by scheduling_date;
</select>
接收参数的具体的实体信息：
//
// Source code recreated from a .class file by IntelliJ IDEA
// (powered by Fernflower decompiler)
//
package com.yl.entity.vo.base;
import com.yl.entity.DateConvertUtils;
import java.io.Serializable;
import java.util.Date;
public class PositionSchedulingVo implements Serializable {
private static final long serialVersionUID = 1L;
private Long demandId;
private Date schedulingDate;
private Long id0;
private Integer type0;
private Date st0;
private Date et0;
private Long id1;
private Integer type1;
private Date st1;
private Date et1;
private Long id2;
private Integer type2;
private Date st2;
private Date et2;
private Long id3;
private Integer type3;
private Date st3;
private Date et3;
public static final String FORMAT_DATE = "yyyy-MM-dd";
public static final String FORMAT_TIME = "yyyy-MM-dd HH:mm:ss";
public PositionSchedulingVo() {
}
public String getSt0String() {
return DateConvertUtils.format(this.getSt0(), "yyyy-MM-dd HH:mm:ss");
}
public void setSt0String(String value) {
this.setSt0(DateConvertUtils.parse(value, "yyyy-MM-dd HH:mm:ss", Date.class));
}
public String getSt3String() {
return DateConvertUtils.format(this.getSt3(), "yyyy-MM-dd HH:mm:ss");
}
public void setSt31String(String value) {
this.setSt3(DateConvertUtils.parse(value, "yyyy-MM-dd HH:mm:ss", Date.class));
}
public String getEt0String() {
return DateConvertUtils.format(this.getEt0(), "yyyy-MM-dd HH:mm:ss");
}
public void setEt0String(String value) {
this.setEt0(DateConvertUtils.parse(value, "yyyy-MM-dd HH:mm:ss", Date.class));
}
public String getEt3String() {
return DateConvertUtils.format(this.getEt3(), "yyyy-MM-dd HH:mm:ss");
}
public void setEt3String(String value) {
this.setEt3(DateConvertUtils.parse(value, "yyyy-MM-dd HH:mm:ss", Date.class));
}
public String getSt2String() {
return DateConvertUtils.format(this.getSt2(), "yyyy-MM-dd HH:mm:ss");
}
public void setSt2String(String value) {
this.setSt2(DateConvertUtils.parse(value, "yyyy-MM-dd HH:mm:ss", Date.class));
}
public String getSt1String() {
return DateConvertUtils.format(this.getSt1(), "yyyy-MM-dd HH:mm:ss");
}
public void setSt1String(String value) {
this.setSt1(DateConvertUtils.parse(value, "yyyy-MM-dd HH:mm:ss", Date.class));
}
public String getEt2String() {
return DateConvertUtils.format(this.getEt2(), "yyyy-MM-dd HH:mm:ss");
}
public void setEt2String(String value) {
this.setEt2(DateConvertUtils.parse(value, "yyyy-MM-dd HH:mm:ss", Date.class));
}
public String getEt1String() {
return DateConvertUtils.format(this.getEt1(), "yyyy-MM-dd HH:mm:ss");
}
public void setEt1String(String value) {
this.setEt1(DateConvertUtils.parse(value, "yyyy-MM-dd HH:mm:ss", Date.class));
}
public String getSchedulingDateString() {
return DateConvertUtils.format(this.getSchedulingDate(), "yyyy-MM-dd");
}
public void setSchedulingDateString(String value) {
this.setSchedulingDate(DateConvertUtils.parse(value, "yyyy-MM-dd", Date.class));
}
public Long getDemandId() {
return this.demandId;
}
public void setDemandId(Long demandId) {
this.demandId = demandId;
}
public Date getSchedulingDate() {
return this.schedulingDate;
}
public void setSchedulingDate(Date schedulingDate) {
this.schedulingDate = schedulingDate;
}
public Long getId1() {
return this.id1;
}
public void setId1(Long id1) {
this.id1 = id1;
}
public Integer getType1() {
return this.type1;
}
public void setType1(Integer type1) {
this.type1 = type1;
}
public Date getSt1() {
return this.st1;
}
public void setSt1(Date st1) {
this.st1 = st1;
}
public Date getEt1() {
return this.et1;
}
public void setEt1(Date et1) {
this.et1 = et1;
}
public Long getId2() {
return this.id2;
}
public void setId2(Long id2) {
this.id2 = id2;
}
public Integer getType2() {
return this.type2;
}
public void setType2(Integer type2) {
this.type2 = type2;
}
public Date getSt2() {
return this.st2;
}
public void setSt2(Date st2) {
this.st2 = st2;
}
public Date getEt2() {
return this.et2;
}
public void setEt2(Date et2) {
this.et2 = et2;
}
public Long getId0() {
return this.id0;
}
public void setId0(Long id0) {
this.id0 = id0;
}
public Integer getType0() {
return this.type0;
}
public void setType0(Integer type0) {
this.type0 = type0;
}
public Date getSt0() {
return this.st0;
}
public void setSt0(Date st0) {
this.st0 = st0;
}
public Date getEt0() {
return this.et0;
}
public void setEt0(Date et0) {
this.et0 = et0;
}
public Long getId3() {
return this.id3;
}
public void setId3(Long id3) {
this.id3 = id3;
}
public Integer getType3() {
return this.type3;
}
public void setType3(Integer type3) {
this.type3 = type3;
}
public Date getSt3() {
return this.st3;
}
public void setSt3(Date st3) {
this.st3 = st3;
}
public Date getEt3() {
return this.et3;
}
public void setEt3(Date et3) {
this.et3 = et3;
}
}
当我们在做处理时间类东西时经常遇到的月末时间相加或者相减的情况。
版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
文章由极客之音整理，本文链接：https://www.bmabk.com/index.php/post/80430.html
