mysql 日期date与字符串string相互转换

1.日期转字符串

　　date_format(date,format)

　　date：需要转换的日期；

　　format：格式化的样式

　　 年：   %Y  显示四位 ： 2021；%y    只显示后两位  ：21
        月：   %M 月份的英文显示：October；%m  月份的阿拉伯显示：01-12；%b 月份的英文缩略显示：Oct；%c  月份的阿拉伯显示：1-12
        日：   %d 阿拉伯显示：00-31；%D 带有英文后缀：1st-31th；%e 阿拉伯显示：1-31；%j   年的天：001-366   
        时：   %H ：00-23；%h：01-12；%I（大写i）：01-12；%k：0-23；%l（小写L）：1-12
        分：   %i：00-59
        秒：   %S：00-59；%s：00-59
        微妙：%f
        AM/PM：%p
        12小时制时间：%r:   02:02:44 PM
        24小时制时间： %T：  14:02:44（因此，我们可以使用%T来替代%H:%i:%s的组合使用）
        周： %W：周的英文显示； %w 周的阿拉伯显示 ：0（星期日）-6（星期六）； %a 周的英文缩略显示：Mon-

　　举例：

SELECT DATE_FORMAT(now(),'%Y-%m-%d %T')

SELECT DATE_FORMAT(sysdate(),'%Y-%m-%d %H:%i:%s')

2.字符串转日期

　　str_to_date(str,format)

　　str：字符形式的日期；

　　format：格式化样式

形式一：将字符串格式化到天

　　举例：

select STR_TO_DATE('2021-03-26 14:51:19','%Y-%m-%d')

说明：

字符串转日期，当只转到天时，数据库会为其自动加上0时0分0秒；

上面转成日期后，实际代表的是：2021年3月26日0时0分0秒。

更多说明，见文末扩展。

形式二：将字符串格式化到小时

说明：分钟和秒会被自动设置成0；

形式三：将字符串格式化到分钟

说明：秒数会被自动设置成0；

3.日期与字符串做对比 

　　在mysql中，日期类型是可以直接和字符串形式的日期直接做对比的

　　date_format示例

　　str_to_date示例 

　　在实际开发过程中，我们通常需要进行日期字符串与日期字段之间做对比，而经过上述实验发现：

　　日期和字符串可以进行直接比较。

4.扩展延伸

2022年2月9日14:30:02

字符串转日期（只转到天），在Navicat的展示形式如下：

关于日期计算

往后推一天：

select STR_TO_DATE( '2022-02-09', '%Y-%m-%d' ) + 1

 

代表的实际日期为：2022年2月10日0时0分0秒。

往后推一秒：

-- 方式1
select STR_TO_DATE( '2022-02-09', '%Y-%m-%d %H' ) + 1
-- 方式2
select STR_TO_DATE( '2022-02-09', '%Y-%m-%d %H:%i' ) + 1
-- 方式3
select STR_TO_DATE( '2022-02-09', '%Y-%m-%d %H:%i:%s' ) + 1

往后推一年

 

字符串转成日期后，无法再转回日期

select DATE_FORMAT(STR_TO_DATE( '2022-02-09', '%Y-%m-%d' ), '%Y-%m-%d'）

但是，oracle可以这样搞。

由上面我们可以知道：

在将字符串转日期时，如果，没有精确到时分秒的话，mysql会自动为其添加0时0分0秒；

日期转字符串，却可以再次转回日期

SELECT STR_TO_DATE(DATE_FORMAT(NOW(), '%Y-%m-%d'), '%Y-%m-%d');

 

按日期区间查询数据

在实际开发过程中，我们经常会按时间区间进行查询统计，所以，这就要求我们关于日期的计算要计算清楚，否则统计的数据将会被遗漏。

举例说明：查询2022年1月8日到2022年2月9日的数据

错误统计是：

 

错误原因：实际只统计到2022年2月9日0时0分0秒，2022年2月9日0时0分1秒~2022年2月9日23时59分59秒的数据并未被统计进去

方式一：字符串转日期；

开始时间：字符串转日期，实际为：2022年1月8号0时0分0秒，所以，要用>=；

结束时间：字符串转日期+1，实际为：2022年2月10号0时0分0秒，所以，要用<；

正确统计应为： 

SELECT
  T.INTO_ADDRESS kaKou,
  COUNT( 1 ) jiShu 
FROM
  SC_PERSON_INFO T 
WHERE
  T.CREATETIME >= STR_TO_DATE( '2022-01-08', '%Y-%m-%d' ) 
  AND T.CREATETIME < STR_TO_DATE( '2022-02-09', '%Y-%m-%d' ) + 1 
  AND T.INTO_ADDRESS IS NOT NULL 
  AND T.INTO_ADDRESS != '' 
GROUP BY
  T.INTO_ADDRESS 
ORDER BY
  T.INTO_ADDRESS 
  LIMIT 0,
  15

方式二：将日期字段转字符串；

如果觉得字符串转日期不好记，那我们可以将日期字段转成字符串，这样，就符合我们的记忆了，还不会出错。

查看代码

SELECT
  T.INTO_ADDRESS kaKou,
  COUNT( 1 ) jiShu 
FROM
  SC_PERSON_INFO T 
WHERE
  DATE_FORMAT( T.CREATETIME, '%Y-%m-%d' ) >= '2022-01-08' 
  AND DATE_FORMAT( T.CREATETIME, '%Y-%m-%d' ) <= '2022-02-09' 
  AND T.INTO_ADDRESS IS NOT NULL 
  AND T.INTO_ADDRESS != '' 
GROUP BY
  T.INTO_ADDRESS 
ORDER BY
  T.INTO_ADDRESS 
  LIMIT 0,
  15

 

2022年2月17日16:15:19

但是，这种方式在数据量大的时候，将会拖慢查询速度，不建议直接对字段进行格式转换；

另外的话，在日常开发中，我们常常需要将查询到的数据，按日期进行降序排列，为了提高查询速度，往往，我们会对其创建索引；

因为id本身就是唯一索引，而且id会随着插入的数据而逐步增大，另外，日期即使精确到秒也会重复，所以，我们完全可以按照主键进行降序排列。

2022年2月20日16:16:51

方式三：手动拼上时分秒。

为结束日期字符串手动补齐时分秒（23:59:59）后，再转日期。

查看代码

SELECT
  T.INTO_ADDRESS kaKou,
  COUNT(1) jiShu
FROM
  SC_PERSON_INFO T
WHERE DATE_FORMAT(T.CREATETIME, '%Y-%m-%d') >= '2022-01-08'
  AND DATE_FORMAT(T.CREATETIME, '%Y-%m-%d %T') <= CONCAT('2022-02-09', ' 23:59:59')
  AND T.INTO_ADDRESS IS NOT NULL
  AND T.INTO_ADDRESS != ''
GROUP BY T.INTO_ADDRESS
ORDER BY T.INTO_ADDRESS
LIMIT 0, 15;

当然，为了代码的易读性，我们可以为开始日期加上00:00:00，再转日期。

查看代码

SELECT
  T.INTO_ADDRESS kaKou,
  COUNT(1) jiShu
FROM
  SC_PERSON_INFO T
WHERE DATE_FORMAT(T.CREATETIME, '%Y-%m-%d %T') >= CONCAT('2022-01-08', ' 00:00:00')
  AND DATE_FORMAT(T.CREATETIME, '%Y-%m-%d %T') <= CONCAT('2022-02-09', ' 23:59:59')
  AND T.INTO_ADDRESS IS NOT NULL
  AND T.INTO_ADDRESS != ''
GROUP BY T.INTO_ADDRESS
ORDER BY T.INTO_ADDRESS
LIMIT 0, 15;

系统当前天的日期时间范围（与上面无关，方便后期直接拿来用）

SELECT STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(), '%Y-%m-%d'), ' 00:00:00'), '%Y-%m-%d %T')
UNION ALL
SELECT STR_TO_DATE(CONCAT(DATE_FORMAT(NOW(), '%Y-%m-%d'), ' 23:59:59'), '%Y-%m-%d %T');

 

写在最后

　　哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

作者：Marydon