玩转MySQL的时间类型:Date、DateTime、TimeStamp、Time
Mysql时间类型介绍
在MySQL中，有四种不同的时间类型：DATE、DATETIME、TIMESTAMP和TIME。它们分别表示不同精度和范围的时间。
- DATE类型
DATE类型用于存储日期，格式为'YYYY-MM-DD'。它存储年、月、日，但不存储时间。例如，'2023-04-19'就是一个DATE类型的值。
- DATETIME类型
DATETIME类型用于存储日期和时间，格式为'YYYY-MM-DD HH:MM:SS'。它存储年、月、日、小时、分钟和秒。例如，'2023-04-19 13:30:45'就是一个DATETIME类型的值。
- TIMESTAMP类型
TIMESTAMP类型用于存储日期和时间，格式为'YYYY-MM-DD HH:MM:SS'。它与DATETIME类型的区别在于，TIMESTAMP类型存储的时间范围只能是从'1970-01-01 00:00:01'到'2038-01-19 03:14:07'，而且它存储的时间是相对于当前时区的UTC时间。例如，如果当前时区是北京时间，那么存储'2023-04-19 13:30:45'的TIMESTAMP类型的值实际上是'2023-04-19 05:30:45'。由于存储的时间是相对于UTC时间的，因此在不同的时区中读取该值时，会根据当前时区自动转换为对应的本地时间。
- TIME类型
TIME类型用于存储时间，格式为'HH:MM:SS'。它存储小时、分钟和秒，但不存储日期。例如，'13:30:45'就是一个TIME类型的值。
总的来说，四种时间类型的区别在于它们存储的精度和范围不同。DATE类型只存储日期，DATETIME类型存储日期和时间，TIMESTAMP类型存储日期和时间并且有时间范围限制，而TIME类型只存储时间。
这几种时间类型怎么相互转换呢？
在MySQL中，可以使用函数将不同类型的时间值进行转换。
- DATE转换为DATETIME
可以使用DATE函数将DATE类型的值转换为DATETIME类型的值。例如：
SELECT DATE('2023-04-19 00:00:00');
输出结果为'2023-04-19 00:00:00'，这就是将'2023-04-19'这个DATE类型的值转换为DATETIME类型的值。
- DATETIME转换为DATE或TIME
可以使用DATE函数或TIME函数将DATETIME类型的值转换为DATE类型或TIME类型的值。例如：
SELECT DATE('2023-04-19 13:30:45');
SELECT TIME('2023-04-19 13:30:45');
输出结果分别为'2023-04-19'和'13:30:45'，这就是将'2023-04-19 13:30:45'这个DATETIME类型的值转换为DATE类型和TIME类型的值。
- TIMESTAMP转换为DATETIME或DATE
可以使用FROM_UNIXTIME函数将TIMESTAMP类型的值转换为DATETIME类型或DATE类型的值。例如：
SELECT FROM_UNIXTIME(1647731405);
输出结果为'2022-03-18 08:30:05'，这就是将1647731405这个TIMESTAMP类型的值转换为DATETIME类型的值。如果只想转换为DATE类型的值，可以在函数中添加第二个参数'%Y-%m-%d'。例如：
SELECT FROM_UNIXTIME(1647731405, '%Y-%m-%d');
输出结果为'2022-03-18'，这就是将1647731405这个TIMESTAMP类型的值转换为DATE类型的值。
- DATE、DATETIME、TIME转换为TIMESTAMP
可以使用UNIX_TIMESTAMP函数将DATE类型、DATETIME类型和TIME类型的值转换为TIMESTAMP类型的值。例如：
SELECT UNIX_TIMESTAMP('2023-04-19');
SELECT UNIX_TIMESTAMP('2023-04-19 13:30:45');
SELECT UNIX_TIMESTAMP('13:30:45');
输出结果分别为'1681574400'、'1681621845'和'50745'，这就是将'2023-04-19'、'2023-04-19 13:30:45'和'13:30:45'这些时间类型的值转换为TIMESTAMP类型的值。
需要注意的是，在进行时间类型的转换时，可能会存在精度损失或溢出等问题，需要根据具体情况进行处理。
java代码怎么读取这几种格式呢
在Java中，可以使用java.sql.Timestamp、java.sql.Date和java.sql.Time类来处理MySQL中的DATETIME、DATE和TIME类型的值。
- 读取DATETIME类型的值
使用ResultSet对象的getTimestamp方法来读取DATETIME类型的值，例如：
ResultSet rs = statement.executeQuery("SELECT datetime_col FROM table_name");
while (rs.next()) {
Timestamp datetimeValue = rs.getTimestamp("datetime_col");
// 处理读取到的值
}
- 读取DATE类型的值
使用ResultSet对象的getDate方法来读取DATE类型的值，例如：
ResultSet rs = statement.executeQuery("SELECT date_col FROM table_name");
while (rs.next()) {
Date dateValue = rs.getDate("date_col");
// 处理读取到的值
}
- 读取TIME类型的值
使用ResultSet对象的getTime方法来读取TIME类型的值，例如：
ResultSet rs = statement.executeQuery("SELECT time_col FROM table_name");
while (rs.next()) {
Time timeValue = rs.getTime("time_col");
// 处理读取到的值
}
需要注意的是，在进行时间类型的读取时，需要确保数据库中的时间值已经正确地映射到Java的时间类型上，同时需要根据具体情况来选择使用合适的时间类型。
- 读取TIMESTAMP类型的值：
在Java中，可以使用java.sql.Timestamp类来处理MySQL中的TIMESTAMP类型的值。在读取TIMESTAMP类型的值时，可以使用ResultSet对象的getTimestamp方法，例如：
ResultSet rs = statement.executeQuery("SELECT timestamp_col FROM table_name");
while (rs.next()) {
Timestamp timestampValue = rs.getTimestamp("timestamp_col");
// 处理读取到的值
}
需要注意的是，getTimestamp方法返回的是java.sql.Timestamp类型的值，其精度为毫秒级别。如果需要精确到纳秒，可以使用getObject方法，并将其转换为java.time.LocalDateTime类型，例如：
ResultSet rs = statement.executeQuery("SELECT timestamp_col FROM table_name");
while (rs.next()) {
Object timestampValue = rs.getObject("timestamp_col");
if (timestampValue instanceof java.time.LocalDateTime) {
java.time.LocalDateTime localDateTimeValue = (java.time.LocalDateTime) timestampValue;
// 处理读取到的值
}
}
需要注意的是，在进行时间类型的读取时，需要确保数据库中的时间值已经正确地映射到Java的时间类型上。此外，MySQL中的TIMESTAMP类型默认的时区为当前服务器的时区，如果需要在不同的时区之间进行转换，需要进行额外的处理。
总体来说，区别就是时间精度一样。