date is storing wrong in mariadb while inserting from java

Question

I have selected date as 8th feb 2019 in angular datepicker and in java side logs it is printed as Fri Feb 08 00:00:00 SGT 2019 but in database it stored as 07/02/2019 16:00:00 I am using datetime format in db side. and java.util.date in java. this is happening very randomly if i try gain with the same date in database it stored as 08/02/2019 00:00:00.

database timezone is SGT.

why is it happening ?

[edited] I have selected date as 8th feb 2019 not 2nd feb .. sorry

What is the exact type of column in the database? – Arvind Kumar Avinash Dec 29 '20 at 09:01 — Arvind Kumar Avinash, Dec 29 '20 at 09:01
I am using DateTime type – Neelima Neelu Dec 29 '20 at 09:02 — Neelima Neelu, Dec 29 '20 at 09:02

score 0 · Answer 1 · answered Dec 29 '20 at 09:11

The SGT in Fri Feb 08 00:00:00 SGT 2019 denotes the timezone, whereas in the DB you have UTC time. Both times are the exactly same moment in history.

Selecting dates in browser always happens in user's timezone. You need to adjust for that if you want the time using UTC.

Arvind Kumar Avinash · Answer 2 · 2020-12-29T09:27:09.117

The date-time API of java.util and their formatting API, SimpleDateFormat are outdated and error-prone. It is recommended to stop using them completely and switch to the modern date-time API. For any reason, if you have to stick to Java 6 or Java 7, you can use ThreeTen-Backport which backports most of the java.time functionality to Java 6 & 7. If you are working for an Android project and your Android API level is still not compliant with Java-8, check Java 8+ APIs available through desugaring and How to use ThreeTenABP in Android Project.

Convert the selected date to OffsetDateTime and save the same into the database. Given below is a demo of how to convert a date string to OffsetDateTime:

import java.time.LocalDate;
import java.time.OffsetDateTime;
import java.time.ZoneId;
import java.time.format.DateTimeFormatter;
import java.util.Locale;

public class Main {
    public static void main(String[] args) {
        String strDate = "08/02/2019";
        DateTimeFormatter dtfForParsing = DateTimeFormatter.ofPattern("dd/MM/uuuu", Locale.ENGLISH);
        OffsetDateTime odt = LocalDate.parse(strDate, dtfForParsing).atStartOfDay(ZoneId.of("Asia/Singapore"))
                .toOffsetDateTime();
        System.out.println(odt);
    }
}

Output:

2019-02-08T00:00+08:00

Now, you can use the OffsetDateTime in your JDBC code as follows:

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

and this is how you can retrieve the value from the database:

String query = "SELECT * FROM mytable";
try (Statement stmt = con.createStatement()) {
    ResultSet rs = stmt.executeQuery(query);

    while (rs.next()) {
        // Assuming column 1 is the datetime column
        OffsetDateTime odt = rs.getObject(1, OffsetDateTime.class));
        System.out.println(odt);
        // In a custom format e.g. display just the date
        System.out.println(odt.format(DateTimeFormatter.ofPattern("dd/MM/uuuu", Locale.ENGLISH)));
    }
} catch (SQLException e) {
    e.printStackTrace();
}

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

score 0 · Answer 3 · answered Dec 29 '20 at 09:29

0

The property serverTimezone with the value SGT should be added to the JDBC connection string:

jdbc:mysql://<address>:<port>/<database>?serverTimezone=SGT

From MySQL documentation: 6.3.11 Datetime types processing

answered Dec 29 '20 at 09:29

fantaghirocco

4,584
6
36
47

date is storing wrong in mariadb while inserting from java

3 Answers3