How to insert Time ( HH:MM:SS) into MySQL Database table in Java?

Question

I've a table with a column TIME type (named myTime). string t ="15:50:00"; How to convert and insert this string into myTime column (HH:MM:SS).

Thank you!

You may even use "varchar" as datatype in mysql table and store it directly as string. Later you can fetch as string and convert to your Date object if needed through Java code — jarvo69, Aug 13 '17 at 05:01
Welcome to Stack Overflow. Please research your question before posting it. In many cases you will find your answer faster through your search engine. If you don’t, when you tell us what you found and what you didn’t find, what you have tried and what you are still missing, we can guide you much more precisely. — Ole V.V., Aug 13 '17 at 06:16
Are you using a `PreparedStatement` for inserting into the database? Or Hibernate, SQL*J or some other fancy mechanism? — Ole V.V., Aug 13 '17 at 06:21
For the duplicate mark: For storing a time-of-day as 15:50:00 into a database, you should prefer *not* to use a `java.util.Date` as in the linked question and its accepted answer. It’s better to use `java.time.LocalTime`. — Ole V.V., Aug 14 '17 at 08:02

score 1 · Answer 1 · edited Aug 13 '17 at 04:57

1

You may use TIME Datatype. For example,

CREATE TABLE tests (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(500) NOT NULL,
    start_time TIME,
    end_time TIME
);

edited Aug 13 '17 at 04:57

answered Aug 13 '17 at 04:55

Sai Munikumar Nerukonda

11
2

i have had a table with a column (named myTime) TIME type. but i can't insert a string t = string t ="15:50:00" into this column. How to convert and insert this string into myTime column. pls! – Dũng Hoàng Aug 13 '17 at 05:03
please use `setString()` for SQL datatype. – Ashraf.Shk786 Aug 13 '17 at 05:26

score 1 · Accepted Answer · answered Aug 13 '17 at 06:50

You can use String data type to represent the Time value, or you can use MySQL Time data type and in your Java code use preparedStatement.setTime(), for example:

Your table is:

CREATE my_table (
    id          INT          PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR2(30) NOT NULL,
    time_from   TIME
);

Your Java code can look like this:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Time;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;

public class MySQLDatabaseDemo {

   Connection conn = null;
   PreparedStatement preparedStatement = null;

   public static Connection getConnection() throws Exception {
      String driver = "org.gjt.mm.mysql.Driver";
      String url = "jdbc:mysql://localhost/databaseName";
      String username = "root";
      String password = "root";
      Class.forName(driver);
      Connection conn = DriverManager.getConnection(url, username, 
                                                    password);
      return conn;
   }

   /**
    * @param args [0] = value of "id"
    *             [1] = value of "name"
    *             [2] = value of "time_from"
    */
   public void insertRowWithTimeDatatype(String[] args) {

      String query = "insert into my_table (id, name, timefrom) " + 
                                   "values (?, ?, ?)";      

      DateFormat sdf = new SimpleDateFormat("hh:mm:ss");
      Date date = sdf.parse(args[2]);
      Time time = new Time(date.getTime());

      try {
         conn = getConnection();  // getConnection() is YOUR method

         preparedStatement = conn.prepareStatement(query);

         preparedStatement.setInt(1, Integer.parseInt(args[0]));
         preparedStatement.setString(2, args[1]);
         preparedStatement.setTime(3, time);

         // Execute statement and return the number of rows affected
         int rowCount = preparedStatement.executeUpdate();
         System.out.println("Number of rows affected: " + rowCount);
      } finally {
         preparedStatement.close();
         conn.close();
      }
   }
}

Please don’t teach the young ones to use the long outdated and notoriously troublesome classes `Date`, `Time` and `SimpleDateFormat`. At least not as the first option and without any reservation. Today we have so much better. — Ole V.V., Aug 13 '17 at 11:23

score 0 · Answer 3 · answered Aug 13 '17 at 05:25

0

You can use setString() to set any SQL data type. Try something like this:

prepStatement.setString("myTime", "15:50:00");

answered Aug 13 '17 at 05:25

Usagi Miyamoto

6,196
1
19
33

Ole V.V. · Answer 4 · 2017-08-13T06:19:32.057

I haven’t got the experience myself, but the best you can do is to keep your time in a LocalTime object in Java and use yourPreparedStatement.setObject(parameterIndex, yourTime); to set the time as a value in your SQL insert or update statement. I’m sure you can find code examples, tutorials, documentation, etc., out there. Please go search.

So where do you get the LocalTime object from?

LocalTime yourTime = LocalTime.parse(t);

(where t is your time string, for example 15:50:00 as in the question)

How to insert Time ( HH:MM:SS) into MySQL Database table in Java?

4 Answers4