Handling MySQL datetimes and timestamps in Java

Question

In a java application what would a good compromise in terms of extracing and inputting date information with a MySQL database using a mix of datetimes and timestamps

User · Answer

The MySQL documentation has information on mapping MySQL types to Java types. In general, for MySQL datetime and timestamps you should use java.sql.Timestamp. A few resources include:

http://dev.mysql.com/doc/refman/5.1/en/datetime.html

http://www.coderanch.com/t/304851/JDBC/java/Java-date-MySQL-date-conversion

How to store Java Date to Mysql datetime...?

http://www.velocityreviews.com/forums/t599436-the-best-practice-to-deal-with-datetime-in-mysql-using-jdbc.html

EDIT:

As others have indicated, the suggestion of using strings may lead to issues.

User · Answer

In Java side  the date is usually represented by the  poorly designed  but that aside  java util Date  It is basically backed by the Epoch time in flavor of a long  also known as a timestamp  It contains information about both the date and time parts  In Java  the precision is in milliseconds   In SQL side  there are several standard date and time types  DATE  TIME and TIMESTAMP  at some DB s also called DATETIME   which are represented in JDBC as java sql Date  java sql Time and java sql Timestamp  all subclasses of java util Date  The precision is DB dependent  often in milliseconds like Java  but it can also be in seconds   In contrary to java util Date  the java sql Date contains only information about the date part  year  month  day   The Time contains only information about the time part  hours  minutes  seconds  and the Timestamp contains information about the both parts  like as java util Date does   The normal practice to store a timestamp in the DB  thus  java util Date in Java side and java sql Timestamp in JDBC side  is to use PreparedStatement setTimestamp     java util Date date   getItSomehow    Timestamp timestamp   new Timestamp date getTime     preparedStatement   connection prepareStatement  SELECT   FROM tbl WHERE ts  gt       preparedStatement setTimestamp 1  timestamp     The normal practice to obtain a timestamp from the DB is to use ResultSet getTimestamp     Timestamp timestamp   resultSet getTimestamp  ts    java util Date date   timestamp     You can just upcast

User · Answer

BalusC gave a good description about the problem but it lacks a good end to end code that users can pick and test it for themselves   Best practice is to always store date-time in UTC timezone in DB  Sql timestamp type does not have timezone info    When writing datetime value to sql db        Convert the time into UTC and build Timestamp object      Timestamp ts   Timestamp valueOf LocalDateTime now ZoneId of  UTC            use setTimestamp on preparedstatement     preparedStatement setTimestamp 1  ts     When reading the value back from DB into java     Read it as it is in java sql Timestamp type      Decorate the DateTime value as time in UTC timezone using atZone method in LocalDateTime class  Then  change it to your desired timezone  Here I am changing it to Toronto timezone   ResultSet resultSet   preparedStatement executeQuery    resultSet next    Timestamp timestamp   resultSet getTimestamp 1   ZonedDateTime timeInUTC   timestamp toLocalDateTime   atZone ZoneId of  UTC     LocalDateTime timeInToronto   LocalDateTime ofInstant timeInUTC toInstant    ZoneId of  America Toronto

[java] Handling MySQL datetimes and timestamps in Java

Examples related to java

Examples related to mysql

Examples related to date

Examples related to jdbc

Examples related to timestamp