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( "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( "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( "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.
