插入后一个小时后的日期时间.夏令时 [英] Datetime behind an hour after insertion. Daylight savings

问题描述

我注意到，当我向表中插入某些日期时，我的MySql数据库正在从DateTime对象中减去一个小时.示例:

I've noticed that my MySql database is subtracting an hour from my DateTime objects when I insert certain dates to my tables. Example:

Insert: 2021-03-29 11:44:14.938
Result: 2021-03-29 10:44:14.938

我正在使用JdbcTemplate.update插入Java.Sql.Timestamp对象(以下为timestamp):

I am inserting Java.Sql.Timestamp object (timestamp below) using JdbcTemplate.update:

jdbcTemplate.update(new PreparedStatementCreator() {
    @Override
    public PreparedStatement createPreparedStatement(Connection con) throws SQLException {
        PreparedStatement stmt = con.prepareStatement(
                "INSERT INTO Table (date) VALUES (?)");
        stmt.setTimestamp(5,timestamp));
        return stmt;
    }
});

仅在2021年3月28日之后的DateTime(英国的夏令时)发生这种情况.如果我在3月28日之前插入，就不会浪费时间.示例:

This is only happening for DateTime on/after 28th March 2021 (which is daylight saving time here in the UK). If I insert before 28th March, no time is lost. Example:

Insert: 2021-03-26 11:44:14.938
Result: 2021-03-26 11:44:14.938

我尝试使用Timestamp而不是DateTime作为MySQL类型，但是它没有任何作用.

I have tried using Timestamp rather than DateTime as the MySQL type but it has no effect.

有人知道如何停止这种行为吗?

Does anyone know how to stop this behaviour?

推荐答案

您可以使用OffsetDateTime.从JDBC 4.2开始，您可以直接使用 java.time类型使用JDBC:

You can use OffsetDateTime. Since JDBC 4.2 , you can use java.time types directly with JDBC:

DateTimeFormatter dtf = DateTimeFormatter.ofPattern("uuuu-MM-dd HH:mm:ss.SSS", Locale.ENGLISH);

OffsetDateTime odt = LocalDateTime.parse("2021-03-29 11:44:14.938", dtf)
                                    .atZone(ZoneId.of("Europe/London"))
                                    .toOffsetDateTime();

PreparedStatement st = conn.prepareStatement("INSERT INTO mytable (columnfoo) VALUES (?)");
st.setObject(1, odt);
st.executeUpdate();
st.close();

通过 Trail:日期了解有关现代日期时间API的信息时间 .

Learn about the modern date-time API from Trail: Date Time.

这篇关于插入后一个小时后的日期时间.夏令时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！