1

I have run into this weird Timestamp to Date Conversion issue in Oracle SQL.

Here is the SQL statement:

String INSERT_SQL = String.format("INSERT INTO AUDIT_TASK (%s, %s, %s, %s) VALUES (AUDIT_TASK_SEQ.nextval,?,?,?)",ID,CLASS_NAME,TASK_STEP_TIMESTAMP,OPERATOR);

java.util.Calendar utcCalendarInstance = Calendar.getInstance(TimeZone .getTimeZone("UTC"));
java.util.Calendar cal = Calendar.getInstance();
final PreparedStatement stmt = con.prepareStatement(INSERT_SQL);
stmt.setString(1, audit.getClassName().getValue());
// Save the timestamp in UTC
stmt.setTimestamp(2,new Timestamp(cal.getTimeInMillis()), utcCalendarInstance);

When I execute this statement, while most of the times the creation_date and task_step_timestamp dates are same, but sometimes I get the task_step_timestamp generated with some bogus dates- like '25-APR-0000' or '00-Jan-0001' etc.

  • ID | Creation_date | Task_step_timestamp
  • 1 |27-APR-2018 17:58:53| 25-APR-0000 09:00:45
  • 2 |27-APR-2018 18:06:25| 00-Jan-0001 09:18:25

The data type of task_step_timestamp column in Oracle DB is 'DATE'.

Can some one suggest the cause of this inconsistent conversion of timestamp to date?

rightCoder
  • 281
  • 1
  • 3
  • 18
  • The `Calendar` and `Timestamp` classes are outmoded. [`java.time`, the modern Java date and time API,](https://docs.oracle.com/javase/tutorial/datetime/) is so much nicer to work with. Have a look and then try for example `stmt.setObject(2, Instant.now());`. – Ole V.V. May 21 '18 at 09:01

1 Answers1

0

I don't understand why you are using String#format here. Just use a regular insert which mentions explicit columns:

String INSERT_SQL = "INSERT INTO AUDIT_TASK (ID, ERROR_MESSAGE, TASK_STEP_TIMESTAMP, OPERATOR) ";
INSERT_SQL += "VALUES (AUDIT_TASK_SEQ.nextval, ?, ?, ?)";
PreparedStatement stmt = con.prepareStatement(INSERT_SQL);

Then bind your values:

stmt.setString(1, audit.getErrorMessage() != null ? audit.getErrorMessage().getValue() : null);
stmt.setTimestamp(2, new Timestamp(cal.getTimeInMillis()), utcCalendarInstance);
stmt.setString(3, audit.getClassName().getValue());

Note carefully that the placeholders, in order from left to right, are for the error message, task step timestamp, and operator. Your original code appears to be binding the parameters out of order. By using an insert statement which explicitly mentions the columns, you may avoid this problem.

Edit:

It also doesn't make sense to me why you are worrying about time zones for your timestamp. Just get the numbers of milliseconds since the epoch, and then let the database store that as UTC:

Timestamp timestamp = new Timestamp(System.currentTimeMillis());
stmt.setTimestamp(2, timestamp);
Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Yes, the order of placeholders was incorrect because I just included a subset of column names in this post. I had other columns in my actual table but I thought of skipping them. I have made the change here but still need to figure out the wrong timestamp to date conversion. – rightCoder May 19 '18 at 10:51
  • If you can articulate the logic behind your timestamp maybe I can make a suggestion. By the way, you should _not_ edit your question as you go along here. That could risk invalidating other people's work. – Tim Biegeleisen May 19 '18 at 10:53
  • Point noted. Basically, I need to keep record of time at which an event was created/finished/errored-out. So, as soon as the this event happens, I record it into the DB with time stored in the task_event_timestamp. The creation_Date is a audit column in oracle DB. Ideally, the values in both these tables should match, and they do so in most of the cases, but sometimes it gives an incorrect date in task_timestamp. – rightCoder May 19 '18 at 11:03
  • Is it because of two different instance types of `Calendar`? Does the `Long` value returned by `cal.getTimeInMillis()` is converted incorrectly when we supply `Calender` instance with a different timezone to the `Timestamp` constructor? – rightCoder May 19 '18 at 11:07
  • I don't think you should be using calendars here. Just get ms since the epoch and insert into your DB. Let Oracle worry about managing it as UTC. – Tim Biegeleisen May 19 '18 at 11:13