Skip to content
Related Articles

Related Articles

Improve Article

Difference Between java.sql.Time, java.sql.Timestamp and java.sql.Date in Java

  • Last Updated : 12 Apr, 2021

Across the software projects, we are using java.sql.Time, java.sql.Timestamp and java.sql.Date in many instances. Whenever the java application interacts with the database, we should use these instead of java.util.Date. The reason is JDBC i.e. java database connectivity uses these to identify SQL Date and Timestamp.

Here let us see the differences between all the three and also their usages with a few examples:

java.sql.Time                               java.sql.Timestamp                             java.sql.Date                                     

Stores hours, minutes. Seconds and milliseconds alone.

Not stores date components and hence apt representation for SQL Time

Stores both Date and Time components

Stores years, months and days alone. 



Not stores time components and hence apt representation for SQL Date

This is dependent and extend java.util.DateThis is dependent and extends java.util.DateThis is independent and does not extend java.util.Date
As not having Date information, Date information is normalized and can be set to 0 to confirm ANSI SQL DATEAs having both Date and Time, explicitly they are givenAs not having Time information, Time information is normalized and can be set to 0 to confirm ANSI SQL Time
Need to have 2 columns one for java.sql.Date and java.sql.Time explicitly.In comparison with java.util.Date, as it is not storing nanosecond details though Date and Time info present, equals method between java.sql.Timestamp and java.util.Date return false only. Only one column is enough to have both Date and Time.Nanosecond difference is much helpful in many scenarios from Java, setTimestamp() method is used to set the timestamp If we use the timestamp as datatype, using DATE() function, we can extract date alone, and using TIME() function, we can extract time alone. Need to have 2 columns one for java.sql.Date and java.sql.Time explicitly from Java, setDate() method is used to set the date.
Just to know about the time value of the occurrence of the action. i.e. whether student available at that time on any day etc.,

This is the biggest advantage. In Java, we can write the below queries and get the Date and Time part respectively as follows : 

–To get Date 

select DATE(<columnname>) from <tablename> 

–To get Time 

select TIME(<columnname>) from <tablename> 

For logging entries, to know about the absolute value of transactional timings, we can use the timestamp

Just to know about the date value of the occurrence of the action. i.e. whether student available on that day

One can think that as java.sql.Timestamp can satisfy the requirement to store both Date and Time, then what is the need to have java.sql.Date and java.sql.Time. Let us see them in detail regarding the scenarios



Example 1: In schools, colleges, workplaces where-ever to find out whether the student/employee is available on a specific day or not, it is enough to keep the java.sql.Date alone

Eg : EmployeeXXX available on certain date or not (or)

StudentXXX present to the school etc.,

So , generally we may be having a table like  (let us check in MySQL)

// Here empOrStuInformation is the name of the table

create table empOrStuInformation

  (

  id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

  empOrStuName varchar(20),

  empOrStuAvailability DATE



  );

Insert queries :

insert into empOrStuInformation(empOrStuName,empOrStuAvailability) values('xxx',now());
insert into empOrStuInformation(empOrStuName,empOrStuAvailability) values('yyy',now()); 
insert into empOrStuInformation(empOrStuName,empOrStuAvailability) values('zzz',now());

now() returns the current date and time in the configured time zone as a string but since we have DATE as datatype, we are getting date alone as the inserted value.

Output:

Java




import java.sql.Date;
public class GFG {
    public static void main(String[] args)
    {
        String empOrStuAvailableDate = "2021-01-01";
        Date utilAvailabilityDate
            = new SimpleDateFormat("yyyy-MM-dd")
                  .parse(empOrStuAvailableDate);
        // jdbc connectivity expects java.sql.Date
        java.sql.Date sqlAvailabilityDate
            = new java.sql.Date(
                utilAvailabilityDate.getTime());
  
        try {
            Class.forName("com.mysql.jdbc.Driver");
  
            // Here test is the databasename
            // username is root and password is root
            Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/test", "root",
                "root");
  
            PreparedStatement pStmt = con.prepareStatement(
                "insert into empOrStuInformation (empOrStuName,empOrStuAvailability) values(?,?)");
            
            // As we are using Date datatype, we are using
            // setDate only...
            pStmt.setString(1, "AAAA");
            
            // The setDate() method is used to set date
            // while setTimestamp() is used to set time.
            pStmt.setDate(2, sqlAvailabilityDate);
            pStmt.executeUpdate();
            pStmt.close();
            con.close();
        }
        catch (Exception ex) {
            System.out.println(ex);
        }
    }
}

Output:

java.sql.Timestamp:

java.sql.Timestamp column is much helpful for logging the entries. Eg: By what time the student /employee reached the premises and left the premises, by what time the record is handed over, by what time the work is done, etc.,

In Mysql, in the below way, we can create the table for the column containing a timestamp.

create table empOrStuInformation1



  (

id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

empOrStuName varchar(20),

 empOrStuAvailability DATE, 

 empOrStuLogEntry timestamp

  );

Java




import java.sql.Date;
import java.sql.Timestamp;
public class GFG {
    public static void main(String[] args)
    {
        String empOrStuAvailableDate = "2021-01-01";
        Date utilAvailabilityDate
            = new SimpleDateFormat("yyyy-MM-dd")
                  .parse(empOrStuAvailableDate);
        
        // jdbc connectivity expects java.sql.Date
        java.sql.Date sqlAvailabilityDate
            = new java.sql.Date(
                utilAvailabilityDate.getTime());
  
        // create a java timestamp object that represents
        // the current time (i.e., a "current timestamp")
        Calendar calendarInstance = Calendar.getInstance();
        
        java.sql.Timestamp sampleJavaTimestampObject
            = new java.sql.Timestamp(
                calendarInstance.getTime().getTime());
        try {
            Class.forName("com.mysql.jdbc.Driver");
            
            // Here test is the databasename
            // username is root and password is root
            Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/test", "root",
                "root");
  
            PreparedStatement pStmt = con.prepareStatement(
                "insert into empOrStuInformation1 (empOrStuName,empOrStuAvailability,empOrStuLogEntry) values(?,?,?)");
              
            // As we are using Date datatype, we are using
            // setDate only...
            pStmt.setString(1, "AAAA");
            
            // The setDate() method is used to set date
            pStmt.setDate(2, sqlAvailabilityDate);
            
            // setTimestamp() is used to set time.
            pStmt.setTimestamp(3,
                               sampleJavaTimestampObject);
            pStmt.executeUpdate();
            pStmt.close();
            con.close();
        }
        catch (Exception ex) {
            System.out.println(ex);
        }
    }
}

Output:

One advantage of having timestamp is :

  1. If we want to get the date, then we can write as select DATE(empOrStuLogEntry) from empOrStuInformation1
  2. If we want to get the time, then we can write as select TIME(empOrStuLogEntry) from empOrStuInformation1

java.sql.TIME

create table empOrStuInformation2



(

id int NOT NULL AUTO_INCREMENT PRIMARY KEY,

empOrStuName varchar(20),

empOrStuAvailability DATE,

empOrStuLogEntry timestamp,

empOrStuEntryTime time

 );

Insert statement via mysql :

INSERT INTO `test`.`empOrStuInformation2` (`id`, `empOrStuName`, `empOrStuAvailability`, `empOrStuLogEntry`, `empOrStuEntryTime`) VALUES (‘1’, ‘BBBB’, ‘2021-01-01’, ‘2021-01-01 12:50:00′, ’10:00:00’);

INSERT INTO `test`.`empOrStuInformation2` (`id`, `empOrStuName`, `empOrStuAvailability`, `empOrStuLogEntry`, `empOrStuEntryTime`) VALUES (‘2’, ‘DDDD’, ‘2021-01-01’, ‘2021-01-01 14:50:00′, ’10:00:00’);

Output:

Java




import java.sql.Date;
import java.sql.Time;
import java.sql.Timestamp;
public class GFG {
    public static void main(String[] args)
    {
        String empOrStuAvailableDate = "2021-01-01";
        Date utilAvailabilityDate
            = new SimpleDateFormat("yyyy-MM-dd")
                  .parse(empOrStuAvailableDate);
        // jdbc connectivity expects java.sql.Date
        java.sql.Date sqlAvailabilityDate
            = new java.sql.Date(
                utilAvailabilityDate.getTime());
  
        // create a java timestamp object that represents
        // the current time (i.e., a "current timestamp")
        Calendar calendarInstance = Calendar.getInstance();
        java.sql.Timestamp sampleJavaTimestampObject
            = new java.sql.Timestamp(
                calendarInstance.getTime().getTime());
        try {
            
            Class.forName("com.mysql.jdbc.Driver");
            
            // Here test is the databasename
            // username is root and password is root
            Connection con = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/test", "root",
                "root");
            
            // Instantiating the Time class
            Time entryTime = new Time(0L);
            
            // Setting time
            entryTime.setTime(
                new java.util.Date().getTime());
            PreparedStatement pStmt = con.prepareStatement(
                "insert into empOrStuInformation2(empOrStuName,empOrStuAvailability,empOrStuLogEntry,empOrStuEntryTime) values(?,?,?,?)");
  
            pStmt.setString(1, "AAAA");
            
            // The setDate() method is used to set date
            pStmt.setDate(2, sqlAvailabilityDate);
            
            // setTimestamp() is used to set timestamp.
            pStmt.setTimestamp(3, sampleJavaTimestampObject);
            
            // setTime() is used to set time.
            pStmt.setTime(4, entryTime);
  
            pStmt.executeUpdate();
            pStmt.close();
            con.close();
        }
        catch (Exception ex) {
            System.out.println(ex);
        }
    }
}

Output:

Conclusion:

Depends upon the requirement, all 3 functionalities are helpful. Java.sql.Date -> Used to record the occurrence, entry, exit, etc., without considering the exact time. Java.sql.Time -> To keep alarm kind of functionalities irrespective of date, Java.sql.Timestamp-> To log the entries perfectly and will be much helpful to find the nature of each and every entry that occurred in the table and hence much helpful in case of recovery. Using Date() and Time() functions we can explicitly, Date part and Time part separately also.

Attention reader! Don’t stop learning now. Get hold of all the important Java Foundation and Collections concepts with the Fundamentals of Java and Java Collections Course at a student-friendly price and become industry ready. To complete your preparation from learning a language to DS Algo and many more,  please refer Complete Interview Preparation Course.




My Personal Notes arrow_drop_up
Recommended Articles
Page :