MySQL 日期类型小数秒的处理

素颜马尾好姑娘i 2021-09-21 12:56 293阅读 0赞

# 一、 MySQL 日期类型对小数秒的支持 #

**注**：本文所使用MySQL版本为 **5.7** 版本。

官方文档：[https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html][https_dev.mysql.com_doc_refman_5.7_en_fractional-seconds.html]

## 1、小数秒位数精度 ##

MySQL 支持小数秒的展示，比如官网的例子，在建表时，指定 DATETIME(6)、TIME(3)的位数：

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDc2MzQ1_size_16_color_FFFFFF_t_70][]

需要注意的是，MySQL 对小数秒的精度支持最大到 6 位，即 DATETIME(6)。并且，会自动进行四舍五入，舍入时不发出警告或错误。此行为符合 SQL 标准，并且不受服务器 sql\_mode 设置的影响。

## 2、超出位数自动四舍五入 ##

例如上面的表 t1:

CREATE TABLE t1 (t TIME(3), dt DATETIME(6));

我们插入两条dt字段精度为 7 位的数据：

INSERT INTO `t1`(`t`, `dt`) VALUES ('12:12:12.668', '2020-10-14 00:00:00.2280005');
    INSERT INTO `t1`(`t`, `dt`) VALUES ('12:12:12.666', '2020-10-14 00:00:00.2280004');

查询结果：select \* from t1;

![20201106141650676.png][]

可以看到，第一条记录小数秒位第七位是5，进1了；第二条记录第七位是4，舍去了。

小数秒位也是可以正常比较的，比如查询：

select * from t1 where dt > '2020-10-14 00:00:00.228000';

返回结果一条：

![20201106142045622.png][]

# 二、注意事项，“23:59:59”插入问题 #

当使用DATETIME、TIMESTAMP存储时，一般我们只关注到秒，而MySQL默认毫秒的精度为 0（这与标准SQL默认值6不同，这是为了与以前的MySQL版本兼容）。但是在一些有效期场景下，当我们存储某一天的起始（“2020-11-06 00:00:00”）或者结束时间（“2020-11-06 23:59:59”），需要注意我们insert插入数据传给MySQL的日期的小数秒，如果小数秒超过了500，默认精度又为0，MySQL就会自动四舍五入，导致存入的数据变成“2020-11-07 00:00:00”，导致业务逻辑出错。

比如，Java中的Date类型是有毫秒的，如果Date对象的毫秒数超过了500毫秒，MySQL就会自动加一秒。所以在这种场景，最好要指定Date的毫秒为0，防止进位。如果使用了一些第三方工具包获取某个日期的结束时间，也要考虑返回的日期毫秒的处理。

## Java 获取某个日期的最后一秒时间，给MySQL使用 ##

下面提供两种方式，两种返回的Date，毫秒数都为0：

**方式一**：

public static Date getStartTime(Date date) {
            Calendar dateStart = Calendar.getInstance();
            dateStart.setTime(date);
            dateStart.set(Calendar.HOUR_OF_DAY, 0);
            dateStart.set(Calendar.MINUTE, 0);
            dateStart.set(Calendar.SECOND, 0);
            dateStart.set(Calendar.MILLISECOND, 0);
            return dateStart.getTime();
        }
    
        public static Date getEndTime(Date date) {
            Calendar dateEnd = Calendar.getInstance();
            dateEnd.setTime(date);
            dateEnd.set(Calendar.HOUR_OF_DAY, 23);
            dateEnd.set(Calendar.MINUTE, 59);
            dateEnd.set(Calendar.SECOND, 59);
            //注意毫秒位设置为0
            dateEnd.set(Calendar.MILLISECOND, 0);
            return dateEnd.getTime();
        }

**方式二**：

public static Date floor(Date date){
            if(date==null){
                return null;
            }
            SimpleDateFormat sm = new SimpleDateFormat("yyyy-MM-dd");
            String day = sm.format(date) +" 00:00:00";
            return parseStringToDate(day,"yyyy-MM-dd HH:mm:ss");
        }
    
        /**
         * 取一天最大的时间
         * @param date
         * @return
         */
        public static Date ceiling(Date date){
            if(date==null){
                return null;
            }
            SimpleDateFormat sm = new SimpleDateFormat("yyyy-MM-dd");
            String day = sm.format(date) +" 23:59:59";
            return parseStringToDate(day,"yyyy-MM-dd HH:mm:ss");
        }
    
        /**
         * 字符串转为Date
         * @param date"yyyy-MM-dd"
         * @return
         */
        public static Date parseStringToDate(String date,String format) {
            if(date==null){
                return null;
            }
            SimpleDateFormat sm = new SimpleDateFormat(format);
            try {
                return sm.parse(date);
            } catch (ParseException e) {
                return null;
            }
        }

简单在本地分析测试了一下，方式一要比方式二字符串转换效率高，推荐方式一。

[https_dev.mysql.com_doc_refman_5.7_en_fractional-seconds.html]: https://dev.mysql.com/doc/refman/5.7/en/fractional-seconds.html
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzIyMDc2MzQ1_size_16_color_FFFFFF_t_70]: /images/20210920/840d4ee058fd4daba25acd379f89ba76.png
[20201106141650676.png]: /images/20210920/1dc207d704c545f198b32f3af46ca7ed.png
[20201106142045622.png]: /images/20210920/7f4d3158b4064e7c8e75895323512489.png