How to insert current time in MySQL using Java

Question

I need to insert the current date and time (milliseconds) into MySQL. I did the following:

long time = System.currentTimeMillis();
java.sql.Timestamp timestamp = new java.sql.Timestamp(time);
System.out.println("Time in milliseconds :" + timestamp);

And I got the time in milliseconds correctly (e.g. 2013-03-21 00:08:33.523). However, I need to insert this information into MySQL database. I tried the following using PreparedStatement

prepStmt.setTimestamp(2,timestamp);

But the time is inserted without milliseconds (e.g. 2013-03-21 00:08:33).

How can I insert the time with milliseconds.

EDIT: The column in the database is of DATETIME type.

@PermGenError you know that timestamp is second based right? — ITroubs
– ITroubs, Commented Mar 20, 2013 at 21:12

clav · Accepted Answer · 2017-02-20 15:42:07Z

10

java.util.Date date = new java.util.Date();
java.sql.Timestamp timestamp = new java.sql.Timestamp(date.getTime());
preparedStatement.setTimestamp(1, timestamp);

edited Feb 20, 2017 at 15:42

answered Mar 20, 2013 at 21:12

clav

4,25134 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ozzie · Accepted Answer · 2013-03-20 21:15:54Z

2

It the column is of type DATETIME use the setDate method of PreparedStatement.

java.sql.Date date = new java.sql.Date(System.currentTimeMillis());
stmnt.setDate(1, date);

answered Mar 20, 2013 at 21:15

Ozzie

11.7k4 gold badges23 silver badges25 bronze badges

5 Comments

user2192774 Over a year ago

Not correct because the variable I am inserting is of type Timestamp.

user2192774 Over a year ago

I got two problems. First, it insert the time as: 00:00:00. Second, it does not get the time in milliseconds.

Ozzie Over a year ago

Owh you're right. Date actually zeroes the hours, minutes, etc. What version of MySQL are you using? DATETIME fields only support fractional seconds since version 5.6.4

Ozzie Over a year ago

Then mysql discards the fractional seconds. You could just store the long in the database and not use the datetime field. Or upgrade to a more recent version.

user2192774 Over a year ago

upgrade did not solve the issue. Can you explain with code about the other solution you proposed. It is not clear to me what do you mean ?

Chinmoy · Accepted Answer · 2022-04-23 23:17:09Z

1

You can create a method getCurrentTimeStamp() and call it from JDBC statement

preparedStatement.setTimestamp(4,getCurrentTimeStamp());

Method:

private static java.sql.Timestamp getCurrentTimeStamp() {
 
    java.util.Date today = new java.util.Date();
    return new java.sql.Timestamp(today.getTime());
 
}

edited Apr 23, 2022 at 23:17

user13769010

answered May 24, 2014 at 16:53

Chinmoy

1,53614 silver badges14 bronze badges

Comments

thébé · Accepted Answer · 2013-03-20 21:29:02Z

0

Since the question has been correctly answered by other member let me add some personal hindsight about the storage of date type in database.

On a personal level I prefer to store date information in the long format. I found it easier to both store and use afterward. Not only most language offers to create a new Date with a long parameter but you won't be stuck with a preset format, you will have the opportunity to format it differently. For example, I have to format date to either English or French format on some website I develop, it's then easier to have a if-else if on some language parameter and the apply a given format depending on the language preference.

However, this method have some drawback. You will need to format it back to a readable format for, let's say, automated reports done by a query that won't use Java to format it back.

-----------------------------ADD CODE-----------------------------

    // Stored as 0 if the date is not initialised
    long time = objInscription.getDatePremierVisionnement() != null ?
        objInscription.getDatePremierVisionnement().getTime() : 0;

-------------------------------GET CODE----------------------------
    // long is store as 0 if the date has not been initialised           
    long long = rs.rsReadColumnLong(1);
    if (long  == 0) {
        someobj.setSomeDate(null);
    } else {
        someobj.setSomeDate(new Date(long));
    }

You will be able to use SimpleFormat easily to display your data.

answered Mar 20, 2013 at 21:29

thébé

1144 bronze badges

2 Comments

Ozzie Over a year ago

That's just extra work. When extracting a Date from the database in Java you can already retrieve it as a Date object so why bother doing that yourself. I presume most other languages support Date objects directly from a query result

thébé Over a year ago

I had some problem making that work. I sure was doing something wrong but on the other hand that worked good enough for what I had to do. I'll keep that in mind next time I get to work with some date. Maybe It was because I used the wrong datatype of MySQL.

Collectives™ on Stack Overflow

How to insert current time in MySQL using Java

4 Answers 4

Comments

5 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

5 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related