0

I have created a date object and formatted it as follows :

    Date dt = new java.util.Date();
    SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS");
    String currentTime = sdf.format(dt);

This formatted date will be used to update a field as date time in the database as follows. 

String sql = "update userdetails set lastupdatedOn = '"+currentTime+"' where name ='userA'";
    getJdbcTemplate().execute(sql);

The issue I am facing is that the date is always a couple of minutes behind the system time.

Edit : The code I have written here is just a sample to demonstrate what I wanted to accomplish. I cannot use CURRENT_TIMESTAMP or getDate() since it would fetch the time from DB sever system. I should be setting the time using java code instead. Though the time fetched by that manner is a couple of minutes behind the system time(the system in which the tomcat server is running) which is the issue I am facing.

arunken
  • 415
  • 3
  • 15
  • I'd use the database to set the field. `now()` (or equivalent); which database are you using? – Elliott Frisch Dec 16 '19 at 04:31
  • have a look at https://www.mkyong.com/jdbc/how-to-insert-date-value-in-preparedstatement/ also https://stackoverflow.com/questions/3677606/how-do-i-set-a-full-date-time-sql-using-java-and-not-just-the-date –  Dec 16 '19 at 04:36
  • use DB date function to set the value, still, a couple of minutes is too late for an app to submit the data! – Sandeep Kumar Dec 16 '19 at 04:36
  • 2
    What you mean "a couple of minutes behind the system time"? Is the `currentTimes` different with the system time, or the value saved in database different with the system/current times? – samabcde Dec 16 '19 at 05:15
  • 2
    We could use a concrete example here. The time of your system clock and the `currentTime` obtained from your code. – Ole V.V. Dec 16 '19 at 05:28
  • 2
    I recommend you don’t use `SimpleDateFormat` and `Date`. Those classes are poorly designed and long outdated, the former in particular notoriously troublesome. Instead use `LocalDateTime` and `DateTimeFormatter`, both from [java.time, the modern Java date and time API](https://docs.oracle.com/javase/tutorial/datetime/). You can pass a `LocalDateTime` to your database using `yourPreparedStatement.setObject(1, youtLocalDateTime);`. – Ole V.V. Dec 16 '19 at 05:35
  • I am using Ms SqlServer and I cannot use the database server to set the field as the app server and DB server can have different time. I should be setting the time using java code since I will have to compare the time saved in the DB with that of the current time that is fetched using java code. – arunken Dec 18 '19 at 04:48
  • The code in the OP is just a sample code and I am using prepared statement in the original code. – arunken Dec 18 '19 at 05:01
  • @samabcde The currentTime is different from the system time. It is a couple of minutes less than the system time. – arunken Dec 18 '19 at 05:03
  • So is the `currentTime` created by `new Date()` already different from the system time, or the `lastupdatedOn` retrieved from database after saving different from `currentTime`? – samabcde Dec 18 '19 at 08:20
  • currentTime is different from the system time. – arunken Dec 18 '19 at 08:38

1 Answers1

3

Do not use string concatenation and to construct Sql which can lead to Sql Injection. Use prepared statement.

Do as below

String sql = "update userdetails set lastupdatedOn = ? where name ='userA'";
getJdbcTemplate().update(sql,new Object[]{dt})

OTOH

If you just want to insert current timestamp use CURRENT_TIMESTAMP.

String sql = "update userdetails set lastupdatedOn = CURRENT_TIMESTAMP where name ='userA'";
getJdbcTemplate().execute(sql)
seenukarthi
  • 8,241
  • 10
  • 47
  • 68
  • I was using prepared statement in the original code. I have made an edit in case if the question isn't clear. – arunken Dec 18 '19 at 04:42