Value '0000-00-00 00:00:00' can not be represented as java.sql.Timestamp
解决办法:
1) 配置URL
jdbc:mysql://127.0.0.1:3306/test?zeroDateTimeBehavior=convertToNull
2) 需要转换为long使用
public static long parseTimestampToLong (ResultSet rs, String column) throws SQLException {
try {
Timestamp ts = rs.getTimestamp(column);
if (ts == null) {
return 0;
}
return ts.getTime();
}
catch (SQLException e) {
if (e.getMessage() != null && e.getMessage().indexOf("not found") > -1) {
// 该字段找不到
throw e;
}
return 0;
}
}
其它:
1) timestamp 插入范围
String: 0000-00-00 00:00:00, ['1970-01-01 08:00:01', '2038-01-19 11:14:07']
long: [0, 2147483647000]
0 -> 0000-00-00 00:00:00
1 -> 1970-01-01 08:00:01
2147483647000 -> '2038-01-19 11:14:07'
2) timestamp 值以UTC格式保存, 与时区有关
更改时区后,select出来后的值根据时区变化增减(win7下必须重启MySQL)
3) DateTime & Timestamp
DateTime | Timestamp |
8个字节储存 | 4个字节储存 |
与时区无关 | 跟随时区变化(值以UTC格式保存,select时候绑定时区) |
4) int & long
int 存储时间 上限 2038-01-19 11:14:07 (转换时候java.util.Date需要乘以1000L)
long 存储时间 无上限