Sql date with timestamp in java

Convert java.util Date to java.sql Date, Timestamp, and Time in Java

This post will discuss how to convert java.util Date to java.sql Date, Timestamp, and Time in Java.

Prerequisite:

We know that Relational databases like Oracle, MySQL supports DATE, TIME, and TIMESTAMP data types. All these data types have a corresponding class in JDBC, and each of them extends java.util.Date . This post will discuss how to convert java.util Date to these classes:

1. Convert java.util.Date to java.sql.Date

Java has two Date classes, one present in the java.util package and the other present in the java.sql package. The java.sql.Date corresponds to SQL DATE containing the year, month, and day information while hour, minute, second, and millisecond information is not present.

The conversion from java.util.Date to java.sql.Date is actually very simple in Java. The Constructor of java.sql.Date expects milliseconds elapsed since Epoch (1 January 1970 00:00:00 GMT) which can be obtained by using getTime() method of java.util.Date object.

Output:

java.util.Date : Thu Jan 24 21:53:32 GMT 2015
java.sql.Date : 2015-01-24

Starting with Java 8, we should prefer using Instant class from java.time package which is similar to java.util Date class but it gives nanosecond accuracy. java.time.LocalDateTime (date-time without a time-zone), java.time.ZonedDateTime (date-time with a time-zone), LocalDate (date without a time-zone) and LocalTime (time without a time-zone) were also introduced in Java 8 and above.

Since SQL data type DATE is date-only, with no time and time-zone information, it is better to use java.time.LocalDate class rather than java.util.Date in Java 8 and above. We can create a LocalDate instance by getting today’s date according to a particular time zone.

Now to convert java.time.LocalDate to java.sql.Date , we can use valueOf() method, a recent addition in Java 8 and above, that can obtain an instance of java.sql.Date from a java.time.LocalDate object.

Источник

Class Timestamp

A thin wrapper around java.util.Date that allows the JDBC API to identify this as an SQL TIMESTAMP value. It adds the ability to hold the SQL TIMESTAMP fractional seconds value, by allowing the specification of fractional seconds to a precision of nanoseconds. A Timestamp also provides formatting and parsing operations to support the JDBC escape syntax for timestamp values.

• 19 , which is the number of characters in yyyy-mm-dd hh:mm:ss
• 20 + s , which is the number of characters in the yyyy-mm-dd hh:mm:ss.[fff. ] and s represents the scale of the given Timestamp, its fractional seconds precision.

Note: This type 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. The Timestamp.equals(Object) method never returns true when passed an object that isn’t an instance of java.sql.Timestamp , because the nanos component of a date is unknown. As a result, the Timestamp.equals(Object) method is not symmetric with respect to the java.util.Date.equals(Object) method. Also, the hashCode method uses the underlying java.util.Date implementation and therefore does not include nanos in its computation.

Due to the differences between the Timestamp class and the java.util.Date class mentioned above, it is recommended that code not view Timestamp values generically as an instance of java.util.Date . The inheritance relationship between Timestamp and java.util.Date really denotes implementation inheritance, and not type inheritance.

Источник

Sql date with timestamp in java

Returns the number of milliseconds since January 1, 1970, 00:00:00 GMT represented by this Timestamp object.

Sets this Timestamp object to represent a point in time that is time milliseconds after January 1, 1970 00:00:00 GMT.

Obtains an instance of Timestamp from a LocalDateTime object, with the same year, month, day of month, hours, minutes, seconds and nanos date-time value as the provided LocalDateTime .

Methods inherited from class java.util.Date

Methods inherited from class java.lang.Object

Constructor Detail

Timestamp

@Deprecated public Timestamp(int year, int month, int date, int hour, int minute, int second, int nano)

Timestamp

public Timestamp(long time)

Constructs a Timestamp object using a milliseconds time value. The integral seconds are stored in the underlying date value; the fractional seconds are stored in the nanos field of the Timestamp object.

Method Detail

setTime

public void setTime(long time)

Sets this Timestamp object to represent a point in time that is time milliseconds after January 1, 1970 00:00:00 GMT.

getTime

Returns the number of milliseconds since January 1, 1970, 00:00:00 GMT represented by this Timestamp object.

valueOf

toString

Formats a timestamp in JDBC timestamp escape format. yyyy-mm-dd hh:mm:ss.fffffffff , where ffffffffff indicates nanoseconds.

getNanos

setNanos

public void setNanos(int n)

equals

equals

Tests to see if this Timestamp object is equal to the given object. This version of the method equals has been added to fix the incorrect signature of Timestamp.equals(Timestamp) and to preserve backward compatibility with existing class files. Note: This method is not symmetric with respect to the equals(Object) method in the base class.

before

after

compareTo

compareTo

hashCode

Returns a hash code value for this object. The result is the exclusive OR of the two halves of the primitive long value returned by the Date.getTime() method. That is, the hash code is the value of the expression:

 (int)(this.getTime()^(this.getTime() >>> 32)) 

The hashCode method uses the underlying java.util.Date implementation and therefore does not include nanos in its computation.

valueOf

public static Timestamp valueOf(LocalDateTime dateTime)

Obtains an instance of Timestamp from a LocalDateTime object, with the same year, month, day of month, hours, minutes, seconds and nanos date-time value as the provided LocalDateTime . The provided LocalDateTime is interpreted as the local date-time in the local time zone.

toLocalDateTime

Converts this Timestamp object to a LocalDateTime . The conversion creates a LocalDateTime that represents the same year, month, day of month, hours, minutes, seconds and nanos date-time value as this Timestamp in the local time zone.

from

Obtains an instance of Timestamp from an Instant object. Instant can store points on the time-line further in the future and further in the past than Date . In this scenario, this method will throw an exception.

toInstant

Converts this Timestamp object to an Instant . The conversion creates an Instant that represents the same point on the time-line as this Timestamp .

Submit a bug or feature
For further API reference and developer documentation, see Java SE Documentation. That documentation contains more detailed, developer-targeted descriptions, with conceptual overviews, definitions of terms, workarounds, and working code examples.
Copyright © 1993, 2023, Oracle and/or its affiliates. All rights reserved. Use is subject to license terms. Also see the documentation redistribution policy.

Источник

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!

Источник