Java date time to sql date time

java.sql.Date, java.sql.Time, and java.sql.Timestamp

Most of database support three forms of datetime fields which are DATE, TIME and TIMESTAMP. Each of these have a corresponding class in JDBC and each of them extend java.util.Date. These three are:

• java.sql.Date represents SQL DATE, stores years, months and days without time components. java.sql.Date is disregarding timezones.
• java.sql.Time represents SQL TIME and only contains information about hour, minutes, seconds and milliseconds without date component.
• java.sql.Timestamp represents SQL TIMESTAMP which contains both Date and Time information to the nanoseconds precision.

Let’s check below table referring to top 5 databases (not in the order) datetime data types:

I putting the source, my reference in the right most column. Correct me if I’m wrong

Working With JDBC Classes

Which class to choose is depends on the SQL type of the field. PreparedStatement has setters for all three values, setDate() for java.sql.Date, setTime() for java.sql.Time and setTimestamp() for java.sql.Timestamp.

Let’s down to example, we create a test table in our database. For this article, I’ll use MySQL.

CREATE TABLE test_datetime ( dtm_date DATE, dtm_time TIME, dtm_timestamp TIMESTAMP, obj_date DATE, obj_time TIME, obj_timestamp TIMESTAMP );

And then we insert current date/time (get via new java.util.Date()) into the date field of an SQL database table.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class SqlDateTimeInsertExample < public static void main(String[] args) throws Exception < // (1) connect to mysql database String url = "jdbc:mysql://localhost/coffeehouse?serverTimezone=Asia/Singapore"; Class.forName("com.mysql.cj.jdbc.Driver"); try (Connection conn = DriverManager.getConnection(url, "barista", "cappuccino")) < // (2) set java.sql.Date, Time, and Timestamp with current Date (and time) java.util.Date utilDate = new java.util.Date(); java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); java.sql.Time sqlTime = new java.sql.Time(utilDate.getTime()); java.sql.Timestamp sqlTS = new java.sql.Timestamp(utilDate.getTime()); // (3) insert java.sql.Date, Time and Timestamp (including objects) to DB String sql = "INSERT INTO test_datetime(" + "dtm_date, dtm_time, dtm_timestamp," + "obj_date, obj_time, obj_timestamp) VALUES (. )"; try (PreparedStatement pst = conn.prepareStatement(sql)) < pst.setDate(1, sqlDate); pst.setTime(2, sqlTime); pst.setTimestamp(3, sqlTS); pst.setObject(4, utilDate); pst.setObject(5, utilDate); pst.setObject(6, utilDate); // (4) execute update pst.executeUpdate(); >> > > 

As in above example, with setObject(int parameterIndex , x Object); we can just give a util.Date to the last three parameters which accept it without problem (this is also happen in another JDBC driver, not only MySQL). But to just use setObject(. ) lazily can cause some problem, including data (or part of data) loss.

Note: The URL suffix ?serverTimezone=Asia/Singapore is to suppress: Exception in thread «main» java.sql.SQLException: The server time zone value ‘Malay Peninsula Standard Time’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

(Can you see the typo in above MySQL JDBC error message? :p )

Connect to mysql to confirm the result:

$ mysql -u barista -p Enter password: ********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.5.27 MySQL Community Server (GPL) mysql> connect coffeehouse Connection id: 10 Current database: coffeehouse mysql> select * from test_datetime; +------------+----------+---------------------+------------+----------+---------------------+ | dtm_date | dtm_time | dtm_timestamp | obj_date | obj_time | obj_timestamp | +------------+----------+---------------------+------------+----------+---------------------+ | 2019-08-15 | 15:48:19 | 2019-08-15 15:48:19 | 2019-08-15 | 15:48:19 | 2019-08-15 15:48:19 | +------------+----------+---------------------+------------+----------+---------------------+ 1 row in set (0.00 sec)

Liked this Tutorial? Share it on Social media!

Источник

How to Convert java.util.Date to java.sql.Date in Java?

Date class is present in both java.util package and java.sql package. Though the name of the class is the same for both packages, their utilities are different. Date class of java.util package is required when data is required in a java application to do any computation or for other various things, while Date class of java.sql package is used whenever we need to store or read the data of DATE type in SQL, also Date class of java.sql package stores information only regarding the date, whereas Date class of java.util package stores both date and time information.

It must be remembered that when we need to convert one data form to another, we must use getTime() method of the Date class of java.util package.Though java.sql.Date class is a subclass of java.util.Date class, we can’t use java.sql.Date class wherever java.util.Date class must be passed, else it will violate the Liskov Substitution principle and our program will throw run time errors on execution, therefore it is not advised to pass SQL Date to methods that expect util date. Let us do discuss getTime() method prior to landing upon the implementation part.

The getTime() method of Java Date class returns the number of milliseconds since January 1, 1970, 00:00:00 GTM which is represented by the Date object.

Parameters: The function does not accept any parameter.

Return Value: It returns the number of milliseconds since January 1, 1970, 00:00:00 GTM.

Exception: The function does not throw any exceptions.

Источник

How to Convert java.util.Date to java.sql.Timestamp

The java.sql.Timestamp extends java.util.Date class, is a thin wrapper to represent SQL TIMESTAMP, which able to keep both date and time. java.sql.Timestamp is a composite of a java.util.Date and a separate nanoseconds value. Only integral seconds are stored in the java.util.Date component. The fractional seconds — the nanos — are separate.

Convert java.util.Date to java.sql.Timestamp

java.util.Date to java.sql.Timestamp conversion is necessary when a java.util.Date object needs to be written in a database which the column is used to store TIMESTAMP. Example of this data are last login datetime, transaction creation datetime, etc. java.sql.Timestamp used by JDBC to identify an TIMESTAMP type.

import java.text.DateFormat; import java.text.SimpleDateFormat; public class UtilDateToSqlTimestampExample < public static void main(String[] args) < java.util.Date utilDate = new java.util.Date(); System.out.println("java.util.Date time : " + utilDate); java.sql.Timestamp sqlTS = new java.sql.Timestamp(utilDate.getTime()); System.out.println("java.sql.Timestamp time: " + sqlTS); DateFormat df = new SimpleDateFormat("dd/MM/YYYY hh:mm:ss:SSS"); System.out.println("Date formatted : " + df.format(utilDate)); >> 

java.util.Date time : Fri Aug 02 01:44:51 SGT 2019 java.sql.Timestamp time: 2019-08-02 01:44:51.596 Date formatted : 02/08/2019 01:44:51:596

Per above example, we can convert java.util.Date to java.sql.Timestamp by using the getTime() method of Date class and then pass that value to the constructor of Timestamp object. Date’s getTime() method will return the long millisecond value of that object.

Convert java.util.Timestamp to java.sql.Date

And vice versa, java.sql.Date to java.util.Date conversion is necessary when we need to read TIMESTAMP value from database, and pass it to a java.util.Date variable.

import java.text.DateFormat; import java.text.SimpleDateFormat; public class SqlTimestampToUtilDateExample < public static void main(String[] args) < java.sql.Timestamp sqlTS = java.sql.Timestamp.valueOf("1997-05-07 21:30:55.888"); System.out.println("java.sql.Timestamp time: " + sqlTS); java.util.Date utilDate = new java.util.Date(sqlTS.getTime()); System.out.println("java.util.Date time : " + utilDate); DateFormat df = new SimpleDateFormat("dd/MM/YYYY hh:mm:ss:SSS"); System.out.println("Date formatted : " + df.format(utilDate)); >> 

java.sql.Timestamp time: 1997-05-07 21:30:55.888 java.util.Date time : Wed May 07 21:30:55 SGT 1997 Date formatted : 07/05/1997 09:30:55:888

Putting it All Together

In following example, we will implement the conversion in a simple SQL INSERT and QUERY example. First, we create a test table in our database. As in previous example, this example also using PostgreSQL database. For the sake of showing the difference between DATE datatype and TIMESTAMP datatype, we’ll create a table with both types:

create table test_date ( curr_date DATE, curr_timestamp TIMESTAMP );

The next program demonstrates every step you need to convert current date into a date and a timestamp field, and insert it to database table.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class SqlTimestampInsertExample < public static void main(String[] args) throws Exception < // (1) connect to postgresql database String url = "jdbc:postgresql://localhost/coffeeshop"; Class.forName("org.postgresql.Driver"); try (Connection conn = DriverManager.getConnection(url, "barista", "espresso")) < // (2) set java.sql.Date and Timestamp with current Date (and time) java.util.Date utilDate = new java.util.Date(); java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); java.sql.Timestamp sqlTS = new java.sql.Timestamp(utilDate.getTime()); // (3) insert java.sql.Date and Timestamp to DB String sql = "INSERT INTO test_date(curr_date, curr_timestamp) VALUES (. )"; try (PreparedStatement pst = conn.prepareStatement(sql)) < pst.setDate(1, sqlDate); pst.setTimestamp(2, sqlTS); // (4) execute update pst.executeUpdate(); >> > > 

And now, check the result using psql (result may very):

$ psql coffeeshop barista Password for user barista: psql (9.2.1) Type "help" for help. coffeeshop=> select * from test_date; curr_date | curr_timestamp ------------+------------------------- 2019-08-02 | 2019-08-02 02:17:35.803 (1 row)

From psql console we can see, that DATE is «2019-08-02» and TIMESTAMP is «2019-08-02 02:17:35.803». Now, we create another program to read from database:

import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class SqlTimestampQueryExample < public static void main(String[] args) throws Exception < // (1) connect to postgresql database String url = "jdbc:postgresql://localhost/coffeeshop"; Class.forName("org.postgresql.Driver"); try (Connection conn = DriverManager.getConnection(url, "barista", "espresso")) < // (2) create statement and query Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery("SELECT * FROM test_date"); while ( rs.next() ) < java.sql.Date currSqlDate = rs.getDate("curr_date"); java.sql.Timestamp currSqlTS = rs.getTimestamp("curr_timestamp"); java.util.Date currDate = new java.util.Date(currSqlTS.getTime()); // (3) print results System.out.println("java.sql.Date : " + currSqlDate); System.out.println("java.sql.Timestamp: " + currSqlTS); System.out.println("java.util.Date : " + currDate); >> > > 

java.sql.Date : 2019-08-02 java.sql.Timestamp: 2019-08-02 02:17:35.803 java.util.Date : Fri Aug 02 02:17:35 SGT 2019

And if you curious, what happen is the developer make mistakes, and the datatypes switched?

java.sql.Timestamp currSqlTS = rs.getTimestamp("curr_date"); java.sql.Date currSqlDate = rs.getDate("curr_timestamp"); java.util.Date currDate = new java.util.Date(currSqlTS.getTime()); System.out.println("java.sql.Date : " + currSqlDate); System.out.println("java.sql.Timestamp: " + currSqlTS); System.out.println("java.util.Date : " + currDate); 

Here the result (date/time may vary):

java.sql.Date : 2019-08-02 java.sql.Timestamp: 2019-08-02 00:00:00.0 java.util.Date : Fri Aug 02 00:00:00 SGT 2019

Although not end of the world, the time component is gone for Timestamp.

Conclusion

java.sql.Timestamp used in JDBC to store (and use to retrieve) a TIMESTAMP value. If you need to convert from java.util.Date to java.sql.Timestamp or vice versa, you can use the method getTime() of the source object to get the millisecond value and pass it to target constructor. And again, if you are using Java 8, then better to use new Date/Time API in java.time.* package.

Liked this Tutorial? Share it on Social media!

Источник

java.sql.Date, java.sql.Time, and java.sql.Timestamp

Most of database support three forms of datetime fields which are DATE, TIME and TIMESTAMP. Each of these have a corresponding class in JDBC and each of them extend java.util.Date. These three are:

• java.sql.Date represents SQL DATE, stores years, months and days without time components. java.sql.Date is disregarding timezones.
• java.sql.Time represents SQL TIME and only contains information about hour, minutes, seconds and milliseconds without date component.
• java.sql.Timestamp represents SQL TIMESTAMP which contains both Date and Time information to the nanoseconds precision.

Let’s check below table referring to top 5 databases (not in the order) datetime data types:

I putting the source, my reference in the right most column. Correct me if I’m wrong

Working With JDBC Classes

Which class to choose is depends on the SQL type of the field. PreparedStatement has setters for all three values, setDate() for java.sql.Date, setTime() for java.sql.Time and setTimestamp() for java.sql.Timestamp.

Let’s down to example, we create a test table in our database. For this article, I’ll use MySQL.

CREATE TABLE test_datetime ( dtm_date DATE, dtm_time TIME, dtm_timestamp TIMESTAMP, obj_date DATE, obj_time TIME, obj_timestamp TIMESTAMP );

And then we insert current date/time (get via new java.util.Date()) into the date field of an SQL database table.

import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; public class SqlDateTimeInsertExample < public static void main(String[] args) throws Exception < // (1) connect to mysql database String url = "jdbc:mysql://localhost/coffeehouse?serverTimezone=Asia/Singapore"; Class.forName("com.mysql.cj.jdbc.Driver"); try (Connection conn = DriverManager.getConnection(url, "barista", "cappuccino")) < // (2) set java.sql.Date, Time, and Timestamp with current Date (and time) java.util.Date utilDate = new java.util.Date(); java.sql.Date sqlDate = new java.sql.Date(utilDate.getTime()); java.sql.Time sqlTime = new java.sql.Time(utilDate.getTime()); java.sql.Timestamp sqlTS = new java.sql.Timestamp(utilDate.getTime()); // (3) insert java.sql.Date, Time and Timestamp (including objects) to DB String sql = "INSERT INTO test_datetime(" + "dtm_date, dtm_time, dtm_timestamp," + "obj_date, obj_time, obj_timestamp) VALUES (. )"; try (PreparedStatement pst = conn.prepareStatement(sql)) < pst.setDate(1, sqlDate); pst.setTime(2, sqlTime); pst.setTimestamp(3, sqlTS); pst.setObject(4, utilDate); pst.setObject(5, utilDate); pst.setObject(6, utilDate); // (4) execute update pst.executeUpdate(); >> > > 

As in above example, with setObject(int parameterIndex , x Object); we can just give a util.Date to the last three parameters which accept it without problem (this is also happen in another JDBC driver, not only MySQL). But to just use setObject(. ) lazily can cause some problem, including data (or part of data) loss.

Note: The URL suffix ?serverTimezone=Asia/Singapore is to suppress: Exception in thread «main» java.sql.SQLException: The server time zone value ‘Malay Peninsula Standard Time’ is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

(Can you see the typo in above MySQL JDBC error message? :p )

Connect to mysql to confirm the result:

$ mysql -u barista -p Enter password: ********** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 5.5.27 MySQL Community Server (GPL) mysql> connect coffeehouse Connection id: 10 Current database: coffeehouse mysql> select * from test_datetime; +------------+----------+---------------------+------------+----------+---------------------+ | dtm_date | dtm_time | dtm_timestamp | obj_date | obj_time | obj_timestamp | +------------+----------+---------------------+------------+----------+---------------------+ | 2019-08-15 | 15:48:19 | 2019-08-15 15:48:19 | 2019-08-15 | 15:48:19 | 2019-08-15 15:48:19 | +------------+----------+---------------------+------------+----------+---------------------+ 1 row in set (0.00 sec)

Liked this Tutorial? Share it on Social media!

Источник