Difference Between java.sql.Time, java.sql.Timestamp and java.sql.Date
Introduction :
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 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 extend java.util.DateThis is independent and does not extend java.util.DateAs 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 TimeNeed 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 enough to have both Date and Time.Nanosecond difference is much helpful in many scenariosNeed to have 2 columns one for java.sql.Date and java.sql.Time explicitly. From Java, setTimestamp() method is used to set the timestampFrom Java, setDate() method is used to set the date
If we use timestamp as datatype, using DATE() function, we can extract date alone and using TIME() function, we can extract time alone. This is the biggest advantage. In Java, we can write below queries and get Date and Time part respectively as follows :
— To get Date
select DATE(<columnname>) from <tablename>
— To get Time
select TIME(<columnname>) from <tablename>
Just to know about the time value of the occurrence of the action. i.e. whether student available on that time on any day etc.,For logging entries, to know about the absolute value of transactional timings, we can use timestampJust 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, work places where ever to find out whether the student/employee is available on the 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;
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.,
Get priya raj’s stories in your inbox
Join Medium for free to get updates from this writer.
In Mysql, in below way we can create the table for the column containing timestamp
create table empOrStuInformation1
(
id int NOT NULL AUTO_INCREMENT PRIMARY KEY,
empOrStuName varchar(20),
empOrStuAvailability DATE,
empOrStuLogEntry timestamp
);
Java way of inserting records for timestamp
Java
import java.sql.Date;
import java.sql.Timestamp;
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 :
If we want to get the date, then we can write as
select DATE(empOrStuLogEntry) from empOrStuInformation1
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 way :
Java
import java.sql.Date;
import java.sql.Timestamp;
import java.sql.Time;
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 alaram 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 explicit Date part and Time part seperately also
