MySQL 日期类型小数秒的处理

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

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

官方文档：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));

需要注意的是，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;

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

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

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

返回结果一条：

 

二、注意事项，“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;
        }
    }

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