Difference Between java.sql.Time, java.sql.Timestamp and java.sql.Date in Java
Last Updated :
31 Oct, 2022
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.Date | This is dependent and extends java.util.Date | This 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 DATE | As having both Date and Time, explicitly they are given | As 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 wherever 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 :
- 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
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.
Similar Reads
Difference Between DateTime and SmallDateTime in SQL Server SQL Server datatypes are used to store date and date and time values in the database, there are various types of date data types available in the SQL. Whenever we manage data in the SQL server database, it’s often very important to choose the right to store the date and time. The following two data
3 min read
Java SQL Timestamp setNanos() function with examples The setNanos() function is a part of Timestamp class of Java SQL.The function is used to set the fractional part of the second's value of the Timestamp Object. The function sets the object's Nanos value to the given value.Function Signature: public void setNanos(int t) Syntax: ts1.setNanos(l); Param
2 min read
Java SQL Timestamp setTime() function with examples The setTime() function is a part of Timestamp class of Java SQL.The function is used to set the time of the Timestamp object. The function takes time in milliseconds which represents the time in milliseconds after 1st January, 1970. Function Signature: public void setTime(long t) Syntax: ts1.setTime
2 min read
Comparing Timestamp Dates With Date-Only Parameter in SQL Working with date and time in databases is a common task, especially when dealing with timestamps. Comparing timestamp values with date-only parameters in SQL is important for filtering data, performing calculations, and ensuring data accuracy.In this article, we will explain the process of comparin
4 min read
How to Get Current Date and Time in SQL? Managing date and time efficiently is crucial for any database system. SQL provides built-in functions to retrieve the current date and time, which are especially useful for applications involving logging, reporting, and auditing. In this article, we will explain the three main SQL functions to fetc
4 min read
java.time.LocalTime Class in Java Java is the most popular programming language and widely used programming language. Java is used in all kinds of applications like mobile applications, desktop applications, web applications. As in Java, java.time.LocalTime class represents time, which is viewed as hour-minute-second. This class is
5 min read