详细分析Mysql中的STR_TO_DATE基本知识（全） - ToB企服应用市场:ToB评测及商务社交产业平台

STR_TO_DATE(str, format)

复制代码

CREATE TABLE employee (
id INT PRIMARY KEY,
name VARCHAR(50),
hire_date VARCHAR(20)
);
INSERT INTO employee (id, name, hire_date) VALUES
(1, 'John Doe', '2020-01-15'),
(2, 'Jane Smith', '2019-09-10'),
(3, 'Alice Johnson', '2021-03-25');

复制代码

SELECT * FROM employee WHERE STR_TO_DATE(hire_date, '%Y-%m-%d') > '2020-01-01';

复制代码

SELECT * FROM employee WHERE STR_TO_DATE(hire_date, '%Y-%m-%d') BETWEEN '2019-01-01' AND '2019-12-31';

复制代码

SELECT * FROM equipment_tyre_repare_order_detail where status = 1 ORDER BY STR_TO_DATE(repare_time, '%Y-%m-%d')

复制代码

SELECT * FROM equipment_tyre_repare_order_detail
WHERE status = 1
AND STR_TO_DATE(DATE_FORMAT(repare_time, '%Y-%m-%d'), '%Y-%m-%d');

复制代码

SELECT * FROM equipment_tyre_repare_order_detail
WHERE status = 1
ORDER BY STR_TO_DATE(
CONCAT(
SUBSTRING_INDEX(repare_time, '-', 1), '-',
LPAD(SUBSTRING_INDEX(SUBSTRING_INDEX(repare_time, '-', -2), '-', 1), 2, '0'), '-',
LPAD(SUBSTRING_INDEX(repare_time, '-', -1), 2, '0')
),
'%Y-%m-%d'
);

复制代码

SELECT LPAD('123', 5, '0');

复制代码

SUBSTRING_INDEX(str, delim, count)

复制代码

SELECT SUBSTRING_INDEX('apple,banana,cherry,grape', ',', 2);

复制代码

SELECT SUBSTRING_INDEX('apple,banana,cherry,grape', ',', -2);

复制代码

/**
* 轮胎更换记录的条件查询
* @param tyreRepareOrderDetailVo
* @param query
* @return
*/
@Override
public IPage<TyreRepareOrderDetail> inquire(TyreRepareOrderDetailVO tyreRepareOrderDetailVo, Query query){
QueryWrapper<TyreRepareOrderDetail> wrapper = new QueryWrapper<TyreRepareOrderDetail>()
.eq("status", 1)
.orderByAsc("equipment_no") // 按照车号升序排列
.orderByDesc("replace_location");
IPage<TyreRepareOrderDetail> pages = super.page(Condition.getPage(query), wrapper);
// 对更换日期进行排序
List<TyreRepareOrderDetail> records = pages.getRecords();
Collections.sort(records, new Comparator<TyreRepareOrderDetail>() {
@Override
public int compare(TyreRepareOrderDetail detail1, TyreRepareOrderDetail detail2) {
// 比较更换日期
int compareResult = compareDateStrings(detail1.getRepareTime(), detail2.getRepareTime());
if (compareResult != 0) {
return compareResult;
}
// 如果更换日期相同，则比较车号
compareResult = detail1.getEquipmentNo().compareTo(detail2.getEquipmentNo());
if (compareResult != 0) {
return compareResult;
}
// 如果车号相同，则比较更换位置
return detail1.getReplaceLocation().compareTo(detail2.getReplaceLocation());
}
// 比较日期字符串
private int compareDateStrings(String dateString1, String dateString2) {
LocalDate date1 = parseDateString(dateString1);
LocalDate date2 = parseDateString(dateString2);
return date2.compareTo(date1); // 降序排序
}
private LocalDate parseDateString(String dateString) {
// 使用 "-" 进行分割
String[] parts = dateString.split("-");
// 根据日期字符串中各部分的值判断是否需要补零
String year = parts[0];
String month = parts[1].length() == 1 ? "0" + parts[1] : parts[1];
String day = parts[2].length() == 1 ? "0" + parts[2] : parts[2];
// 拼接成完整的日期字符串
String formattedDate = String.format("%s-%s-%s", year, month, day);
// 使用 DateTimeFormatter 解析日期字符串
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
return LocalDate.parse(formattedDate, formatter);
}
});
// 更新结果集，不设置这个一直都是单个页面的排序
pages.setRecords(records);
return pages;
}

复制代码

import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.util.Comparator;
public class DateComparator implements Comparator<String> {
@Override
public int compare(String dateString1, String dateString2) {
LocalDate date1 = parseDateString(dateString1);
LocalDate date2 = parseDateString(dateString2);
return date2.compareTo(date1); // 降序排序
}
private LocalDate parseDateString(String dateString) {
// 使用 "-" 进行分割
String[] parts = dateString.split("-");
// 根据日期字符串中各部分的值判断是否需要补零
String year = parts[0];
String month = parts[1].length() == 1 ? "0" + parts[1] : parts[1];
String day = parts[2].length() == 1 ? "0" + parts[2] : parts[2];
// 拼接成完整的日期字符串
String formattedDate = String.format("%s-%s-%s", year, month, day);
// 使用 DateTimeFormatter 解析日期字符串
DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
return LocalDate.parse(formattedDate, formatter);
}
}

复制代码