Datetime behind an hour after insertion. Daylight savings

Question

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

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;
    }
});

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

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?

You seem to have a lot of evidence that this is a DST issue. — Gordon Linoff, Jan 20 '21 at 15:33
If you have not done so already, you may want to read: https://medium.com/@kenny_7143/mysterious-daylight-saving-time-in-mysql-38844dd5f807 — PM 77-1, Jan 20 '21 at 15:36
I recommend you don’t use `Timestamp`. That class is poorly designed and long outdated. Instead use `LocalDateTime` (for a `datetime` column) or `OffsetDateTime` (for a `timestamp` column); both are from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). — Ole V.V., Jan 20 '21 at 16:08

score 3 · Accepted Answer · answered Jan 20 '21 at 16:10

Not sure why this was happening, but I fixed the problem by ditching Java.Sql.Timestamp in favour of Java.Time.LocalDateTime.

My insertion code now looks like below (where localDateTime is of type LocalDateTime rather than Timestamp):

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

The MySql database no longer automatically adjusts for timezone.

Arvind Kumar Avinash · Answer 2 · 2021-01-20T16:42:09.680

3

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();

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

edited Jan 20 '21 at 16:42

answered Jan 20 '21 at 16:14

Arvind Kumar Avinash

71,965
6
74
110

Thanks. What if I just insert a LocalDateTime object to the Datetime column of my table (as in my own response below). It seems to address the original issue but you're saying OffsetDateTime instead? Is one better than the other for this use case? – Ben123 Jan 20 '21 at 16:22
2

It depends on the data type in MySQL.A `timestamp` column defines a point in time and requires a Java object that defines a point in time: `OffsetDateTime`. A `datetime` column lacks time zone or UTC offset and hence does not define a point in time. Here I prefer to use a java object that lacks offset too, `LocalDateTime`. – Ole V.V. Jan 20 '21 at 17:14
The JDBC specification only defines support for `OffsetDateTime` for `TIMESTAMP WITH TIME ZONE` columns, not for date/time without time zone information. – Mark Rotteveel Jan 21 '21 at 12:07
True, @MarkRotteveel. MySQL ensures (the best it can) that a `timestamp` column is in UTC (which is probably non-standard). – Ole V.V. Jan 22 '21 at 06:48
@Ben123 - Please check https://stackoverflow.com/a/65392564/10819573 – Arvind Kumar Avinash Jan 22 '21 at 07:24

Datetime behind an hour after insertion. Daylight savings

2 Answers2

Linked

Related