详细分析Mysql中的STR_TO_DATE基本知识(全)

目录

  • 前言
  • 1. 基本知识
  • 2. Demo
  • 3. 实战Demo
  • 4. Sql彩蛋
    • 4.1 LPAD函数
    • 4.2 SUBSTRING_INDEX函数
  • 5. Java彩蛋

前言

对于该知识点,主要因为数据库类型为String(类似2024-03-26),放置于后端操作后,需要自定义比较,而且不是在sql内存做处理,后续特别麻烦

1. 基本知识

STR_TO_DATE是MySQL中的一个日期时间处理函数,用于将字符串转换为日期时间类型。

它的基本用法是将一个包含日期时间信息的字符串转换为对应的日期时间类型

基本的语法如下:

STR_TO_DATE(str, format)
  • str:要转换为日期时间的字符串
  • format:指定了输入字符串的日期时间格式

返回一个日期时间类型的值,或者在无法解析输入字符串时返回NUL

对应的日期格式如下:

format参数定义了输入字符串的日期时间格式,可以包含各种日期时间格式化符号,如%Y%m%d等,用来表示年、月、日等不同部分

具体的格式化符号及其含义如下:(注意是两位数字。这里埋一个伏笔,如果遇到不是两位的也可做处理

  • %Y:四位年份
  • %y:两位年份
  • %m:两位月份
  • %c:月份(0-12)
  • %d:两位日期
  • %H:小时(00-23)
  • %h:小时(01-12)
  • %i:两位分钟
  • %s:两位秒数
  • %p:AM或PM

简单的示例如下:

  • 将字符串’2024-03-28’转换为日期类型SELECT STR_TO_DATE('2024-03-28', '%Y-%m-%d');,返回值为2024-03-28
  • 将字符串’Mar 28, 2024 10:30:00 PM’转换为日期时间类型SELECT STR_TO_DATE('Mar 28, 2024 10:30:00 PM', '%b %d, %Y %h:%i:%s %p');,返回值为2024-03-28 22:30:00

2. Demo

employee的表,其中有一个字段hire_date存储了员工的入职日期,类型为字符串

现在想将这个字段转换为日期类型,并进行一些基本的查询操作

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');

查询入职日期在2020年之后的员工:

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

查询入职日期在2019年的员工:

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

3. 实战Demo

类似如下Demo

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

最终截图如下:

在这里插入图片描述

如果遇到无法解析的情况可以更换为如下:
(日期字段repare_time有一位月份或日期的情况,可以使用DATE_FORMAT函数来处理,DATE_FORMAT函数允许您指定日期的格式,以便正确解析日期字符串)

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

如果还是不行,可以再次升级:
使用如下代码:(LPAD函数来确保日期和月份是两位数,使用STR_TO_DATE函数将其转换为日期对象,并对其进行排序)

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'
);

4. Sql彩蛋

上述Sql中提及LPAD以及SUBSTRING_INDEX函数

知识点补充如下:

4.1 LPAD函数

LPAD是MySQL中的一个字符串函数,用于向一个字符串的左侧添加指定的字符,直到达到指定的长度

它的基本语法如下:LPAD(str, len, padstr)

  • str:要进行填充的字符串
  • len:填充后字符串的长度
  • padstr:要填充的字符或子字符串

Demo如下:

假有一个字符串’123’,现在希望将其填充到长度为5,用字符’0’进行填充,即在字符串的左侧填充两个’0’,使其长度达到5

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

最终得到结果00123

4.2 SUBSTRING_INDEX函数

SUBSTRING_INDEX 是 MySQL 中的一个字符串函数,用于从一个字符串中获取子字符串,基于指定的分隔符和索引位置

SUBSTRING_INDEX(str, delim, count)
  • str:要处理的字符串
  • delim:分隔符,用于标识子字符串的边界
  • count:要返回的子字符串的数量

从字符串 str 的开头或结尾开始,根据 delim 分隔符将其分割为多个子字符串,并返回其中的第 count 个子字符串

  • 如果 count 为正数,则从字符串开头开始计数
  • 如果 count 为负数,则从字符串结尾开始计数
  • 如果 count 为0,则返回整个字符串

Demo:

  1. 假设有一个字符串 ‘apple,banana,cherry,grape’,现在希望从该字符串中获取第二个逗号分隔的子字符串,即 ‘banana’
SELECT SUBSTRING_INDEX('apple,banana,cherry,grape', ',', 2);

返回apple,banana

  1. 从字符串的末尾开始计数,可以使用负数作为 count 参数,例如获取倒数第二个逗号后的子字符串
SELECT SUBSTRING_INDEX('apple,banana,cherry,grape', ',', -2);

返回 cherry,grape

5. Java彩蛋

置于为何要分析这个函数,源头在于一开始我用的Java代码处理,但是一直无法生效排序,后续才使用Sql内存来处理,总体而言推荐使用Sql来处理,以下代码为题外话

在这里插入图片描述

代码如下:

/**
 * 轮胎更换记录的条件查询
 * @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;
}

后续将其整理成自定义的排序模块:(类似模版使用,专门处理数据库为String,但是后端排序需要使用日期格式)
总体而言还是推荐使用Sql内存排序,毕竟取出在Java代码排序也费事费时

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);
    }
}

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处:/a/506906.html

如若内容造成侵权/违法违规/事实不符,请联系我们进行投诉反馈qq邮箱809451989@qq.com,一经查实,立即删除!

相关文章

做了盲/埋孔,PCB还有必要做盘中孔吗?

在PCB设计中&#xff0c;过孔类型可分为盲孔、埋孔和盘中孔&#xff0c;它们各自有不同应用场景和优势&#xff0c;盲孔和埋孔主要用于实现多层板之间的电气连接&#xff0c;而盘中孔是元器件的固定及焊接。如果PCB板上做了盲孔和埋孔&#xff0c;那么有必要做盘中孔吗&#xf…

java的Class文件分析

文章目录 1. 简介2. Class文件分析 1. 简介 Java有一个著名的口号一次编译&#xff0c;处处运行&#xff0c;这就凸显出来Java程序的一个特点平台无关性。Java的平台无关性是基于各种不同平台的Java虚拟机&#xff0c;以及所有平台都统一支持的程序存储格式—字节码实现的。在…

B端管理系统:UI设计师为什么没有话语权?

一、六大因素&#xff0c;导致了UI设计师话语权缺失。 专业性差异&#xff1a; UI设计师主要负责界面设计和用户体验&#xff0c;而在B端管理系统中&#xff0c;功能性和操作流程往往更为重要&#xff0c;需要产品经理和开发人员更多的参与&#xff0c;他们对于系统的功能和技…

Springboot Thymeleaf 实现数据添加、修改、查询、删除

1、引言 在Spring Boot中使用Thymeleaf模板引擎实现数据的添加、修改、查询和删除功能&#xff0c;通常步骤如下&#xff1a; 在Controller类中&#xff0c;定义处理HTTP请求的方法。创建Thymeleaf模板来处理表单的显示和数据的绑定。 2、用户数据添加 1、 在Controller类中…

尚医通day1

1 创建项目 doc 窗口 pnpm create vite 填写项目名 vue-syt选择框架 vuetypeScript 2整理项目 删除 /src/assets/vue.svg 文件&#xff0c;删除 /src/components 下的 helloWorld.vue删除app.vue内容&#xff0c;快捷键v3ts 生成模板内容去掉 /src/style.css 样式文件&…

格雷希尔G10系列L150A和L200A气动快速连接器,在新能源汽车线束线缆剥线后的气密性测试密封方案

线束线缆在很多用电环境都有使用&#xff0c;比如说新能源汽车&#xff0c;从电池包放电开始&#xff0c;高低压、通讯都开始进行工作&#xff0c;线束在连接的地方需要具有较高的气密性和稳定性&#xff0c;才能保证车辆在不同环境下能够正常的运行。 线束在组装铜鼻子前需要剥…

【Linux】开始掌握进程控制吧!

送给大家一句话&#xff1a; 我并不期待人生可以一直过得很顺利&#xff0c;但我希望碰到人生难关的时候&#xff0c;自己可以是它的对手。—— 加缪 开始学习进程控制 1 前言2 进程创建2.1 fork函数初识2.2 fork函数返回值2.3 写时拷贝2.4 fork常规用法2.5 fork调用失败的原因…

高阶DS---AVL树详解(每步配图)

目录 前言&#xff1a; AVL树的概念: AVL树节点的定义&#xff1a; AVL树的插入&#xff08;重点&#xff09; AVL树的旋转&#xff1a; &#xff08;1&#xff09;新节点插入较高左子树的左侧---右单旋 &#xff08;2&#xff09;新节点插入较高右子树的右侧---左单旋 …

(九)Docker的认识

1.初识Docker 1.1.什么是Docker 微服务虽然具备各种各样的优势&#xff0c;但服务的拆分通用给部署带来了很大的麻烦。 分布式系统中&#xff0c;依赖的组件非常多&#xff0c;不同组件之间部署时往往会产生一些冲突。在数百上千台服务中重复部署&#xff0c;环境不一定一致…

【更新】单细胞联合MR这样筛选靶点|衰老+NK细胞+免疫浸润

今天给大家分享一篇JCR一区&#xff0c;单细胞MR的文章&#xff1a;Unraveling the mechanisms of NK cell dysfunction in aging and Alzheimer’s disease: insights from GWAS and single-cell transcriptomics 标题&#xff1a;揭示NK细胞在衰老和阿尔茨海默病中功能失调的…

Pangolin_FOUND to FALSE so package “Pangolin“ is considered to be NOT FOUND.

修改CMakeLists.txt如下&#xff1a; 在find_package(Eigen3 REQUIRED)后加NO_MUDULE, find_package(Eigen3 REQUIRED NO_MODULE)编译成功&#xff1a;

挖一挖:PostgreSQL Java里的double类型存储到varchar精度丢失问题

前言 大概故事是这样的&#xff0c;PostgreSQL数据库&#xff0c;表结构&#xff1a; create table t1(a varchar);然后使用标准的Java jdbc去插入数据&#xff0c;其基本代码如下&#xff1a; import java.sql.*; public class PgDoubleTest {public static void main(Stri…

Bridge Champ与Ignis公链:探索Web3游戏的新未来

在数字化和去中心化的浪潮中&#xff0c;Web3游戏与公链的融合为游戏行业带来了新的变革。特别是&#xff0c;Bridge Champ和Ignis公链的结合&#xff0c;展示了一种全新的游戏生态模式&#xff0c;不仅为玩家提供了更加公平、透明的游戏体验&#xff0c;同时也为游戏开发和运营…

狐臭的等比数列

题目 #include <bits/stdc.h> using namespace std; #define int long long #define pb push_back #define fi first #define se second #define lson p << 1 #define rson p << 1 | 1 const int maxn 1e6 5, inf 1e9, maxm 4e4 5; const int N 1e6;co…

乡村智慧化:数字乡村助力农村可持续发展

目录 一、数字乡村的内涵与特征 二、数字乡村助力农村可持续发展的路径 &#xff08;一&#xff09;提升农业生产效率 &#xff08;二&#xff09;推动农村产业融合发展 &#xff08;三&#xff09;优化乡村治理模式 &#xff08;四&#xff09;促进乡村生态文明建设 三…

基于ssm校园活动管理平台论文

摘 要 使用旧方法对校园活动信息进行系统化管理已经不再让人们信赖了&#xff0c;把现在的网络信息技术运用在校园活动信息的管理上面可以解决许多信息管理上面的难题&#xff0c;比如处理数据时间很长&#xff0c;数据存在错误不能及时纠正等问题。 这次开发的校园活动管理平…

Python学习笔记-Flask接口创建与测试

服务端: 1.引包 导入 from flask import Flask, request, render_template, redirect Flask: Flask 是一个类&#xff0c;用于创建 Flask web 应用的实例。每个 Flask 应用都从创建这个类的实例开始。示例&#xff1a;app Flask(__name__) request: request 是一个全局对象…

【BlossomConfig】什么是配置中心?以及如何实现一个配置中心?

文章目录 什么是配置中心&#xff1f;如何自己设计一个配置中心&#xff1f; 网关项目源码 RPC项目源码 配置中心项目源码 什么是配置中心&#xff1f; 在单体架构的时候我们可以将配置写在配置文件中&#xff0c;但有⼀个缺点就是每次修改配置都需要重启服务才能生效。 当应用…

MySQL使用技巧,高级Java开发必看

insert into tab(col1,col2…) select … 5、活用正则表达式 regexp ^ $ . * | 6、关联查询比子查询效率快&#xff0c;优先使用join关联查询 7、if(exp,v1,v2) if()函数的使用 exp:表达式 v1:exp为真时返回的值 v2:exp为假时返回的值 8、case when… then… else… en…

D34118电话机免提通话电路应用方案

1、 概述&#xff1a; D34118免提语音通话电路包含了必要的放大器、衰减器、背景噪声检测和控制算法形成高品质的免提通话系统。它包括一个麦克风可调增益放大器、静音控制、发射和接收衰减器&#xff0c;还包括两个线路驱动放大器&#xff0c;可用于形成一个与外部耦合变压器连…