MySQL中时间类型转换

发表于 | 分类于
本文字数: 819 | 阅读时长 ≈ 3 分钟

image

在 MySQL 中需要记录时间时，通常使用 DATE 来表示日期，DATETIME 来表示时间，本文主要讲讲这两个类型之间的转换。

DATE 到 DATETIME

从 DATE 到 DATETIME 时，因为 DATE 中没有时分秒的概念，因此转为 DATETIME 时，直接在 DATE 后加上 00:00:00 即可

DATETIME 到 DATE

在不同 MySQL 版本中，转换处理方式是不同的。

MySQL 5.5

在 MySQL5.5 之前，DATETIME 转 DATE 时是直接舍去时分秒的：

以下摘自MySQL5.5文档：

You can to some extent assign values of one date type to an object of a different date type. However, there may be some alteration of the value or loss of information:

  • If you assign a DATE value to a DATETIME or TIMESTAMP object, the time part of the resulting value is set to ‘00:00:00’ because the DATE value contains no time information.
  • If you assign a DATETIME or TIMESTAMP value to a DATE object, the time part of the resulting value is deleted because the DATE type stores no time information.
  • Remember that although DATETIME, DATE, and TIMESTAMP values all can be specified using the same set of formats, the types do not all have the same range of values. For example, TIMESTAMP values cannot be earlier than 1970 UTC or later than ‘2038-01-19 03:14:07’ UTC. This means that a date such as ‘1968-01-01’, while legal as a DATETIME or DATE value, is not valid as a TIMESTAMP value and is converted to 0.

MySQL 5.6

而在 MySQL5.6 之后的版本，DATETIME 转 DATE 时需要考虑毫秒数，当毫秒数小于 500 时，直接舍去时分秒；而当毫秒数大于(等于) 500 时，则会在将秒数 +1后舍弃时分秒，在这种情况下，如果时间为 2020-09-16 23:59:59.500 ，转换后的 DATE 将是实际的后一天（2020-09-17）。

以下摘自MySQL5.6文档：

Conversion of DATETIME and TIMESTAMP values:

  • Conversion to a DATE value takes fractional seconds into account and rounds the time part. For example, ‘1999-12-31 23:59:59.499’ becomes ‘1999-12-31’, whereas ‘1999-12-31 23:59:59.500’ becomes ‘2000-01-01’.

  • Conversion to a TIME value discards the date part because the TIME type contains no date information.

毫秒导致跨天问题

在某些对时间精度要求高的系统中，写入数据库中 DATE 类型的字段时，需要避免产生因毫秒数导致的跨天问题。以java中的 java.util.Date 为例，这个类是带毫秒时间的，如果使用这个类直接映射数据库 DATE 字段，那么写入时就有可能产生上述的跨天问题。

设置时间部分为0

因为这个问题是由于毫秒数导致的，所以将毫秒设置为0就可以避免：

1
2
3
4
Calendar calendar = Calendar.getInstance();
calendar.setTime(date);
calendar.set(Calendar.MILLISECOND, 0);
date = calendar.getTime();

转为字符串写入

将日期格式化为字符串后，使用字符串类型写入(格式化日期后直接丢弃时间部分)：

1
String dateStr = new SimpleDateFormat("yyyy-MM-dd").format(date);

使用 MyBatis的话，可以使用类型处理器(TypeHandler)：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@MappedJdbcTypes({JdbcType.DATE})
@MappedTypes({Date.class})
public class DateTypeHandler extends BaseTypeHandler<Date> {

    @Override
    public void setNonNullParameter(PreparedStatement ps, int i, Date parameter, JdbcType jdbcType) throws SQLException {
        ps.setString(i, DateUtils.formatDate(parameter)); // 格式化为字符串后写入
    }

    @Override
    public Date getNullableResult(ResultSet rs, String columnName) throws SQLException {
        return (Date) rs.getObject(columnName);
    }

    @Override
    public Date getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
        return (Date) rs.getObject(columnIndex);
    }

    @Override
    public Date getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
        return (Date) cs.getObject(columnIndex);
    }
}

参考

MySQL5.5文档时间类型说明

MySQL5.6文档时间类型转换