Insert Timestamp Value in a Database Using JDBC

Timestamp datatype in SQL is similar to Date (in SQL) both store day:month:year:hour:minute:second. In addition to this timestamp stores fractional seconds too.

Inserting Timestamp in to a database

The PreparedStatement interface provides a method named setTimestamp() this method accepts two parameters an integer variable representing the parameter index of the place holder at which you need to store the timestamp and a long variable representing the number of milliseconds from the epoch time(standard base time I.e. January 1, 1970, 00:00:00 GMT) to the required time.

Example

Assume we have a table in the database named dises with the following description −

+------------------+--------------+------+-----+-------------------+
| Field            | Type         | Null | Key | Default           |
+------------------+--------------+------+-----+-------------------+
| Product_Name     | varchar(100) | YES  |     | NULL              |
| Name_Of_Customer | varchar(100) | YES  |     | NULL              |
| Time_Of_Dis | timestamp    | NO   |     | CURRENT_TIMESTAMP |
| Location         | varchar(100) | YES  |     | NULL              |
+------------------+--------------+------+-----+-------------------+

As you can observe this table contains a column named Time_Of_Dis which stores a timestamp value.

We can store timestamp in to this using the setTimestamp() method of the PreparedStatement interface and retrieve it using the getTimestamp() method of the ResultSet interface.

Following JDBC program stores records into the dises table and retrieves them −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.Timestamp;
public class TimeStampExample {
   public static void main(String args[])throws Exception {
      //Getting the connection
      String mysqlUrl = "jdbc:mysql://localhost/sampledb";
      Connection con = DriverManager.getConnection(mysqlUrl, "root", "password");
      System.out.println("Connection established......");
      //Inserting values to a table
      String query = "INSERT INTO Dises VALUES (?, ?, ?, ?)";
      PreparedStatement pstmt = con.prepareStatement(query);
      pstmt.setString(1, "KeyBoard");
      pstmt.setString(2, "Amith"); pstmt.setTimestamp(3, new Timestamp(1567296000000L));
      pstmt.setString(4, "Hyderabad");
      pstmt.execute();
      pstmt.setString(1, "Earphones");
      pstmt.setString(2, "Sumith"); pstmt.setTimestamp(3, new Timestamp(1556668800000L));
      pstmt.setString(4, "Vishakhapatnam");
      pstmt.execute();
      pstmt.setString(1, "Mouse");
      pstmt.setString(2, "Sudha"); pstmt.setTimestamp(3, new Timestamp(1551398399000L));
      pstmt.setString(4, "Vijayawada");
      pstmt.execute();
      System.out.println("Records inserted......");
      //Creating Statement object
      Statement stmt = con.createStatement();
      ResultSet rs = stmt.executeQuery("select * from Dises");
      //Retrieving values
      while(rs.next()) {
         System.out.println("Product Name: "+rs.getString("Product_Name"));
         System.out.println("Name Of The Customer: "+rs.getString("Name_Of_Customer"));
         System.out.println("Time Of Dis: "+rs.getTimestamp("Time_Of_Dis"));
         System.out.println("Location: "+rs.getString("Location"));
         System.out.println();
      }
   }
}

Output

Connection established......
Records inserted......
Product Name: KeyBoard
Name Of The Customer: Amith
Time Of Dis: 2019-09-01 05:30:00.0
Location: Hyderabad
Product Name: Earphones
Name Of The Customer: Sumith
Time Of Dis: 2019-05-01 05:30:00.0
Location: Vishakhapatnam
Product Name: Mouse
Name Of The Customer: Sudha
Time Of Dis: 2019-03-01 05:29:59.0
Location: Vijayawada