Insertion issue in Oracle database

[pas out]

Hi,

I have an application running in Weblogic(10.0) on linux server. The server is in MST.
The database is Oracle (10g) . The server is in CST.

Here is my code which is inserting the data into Oracle DB.

String MSTTime = null;
Calendar currentdate = Calendar.getInstance();
DateFormat formatter = new SimpleDateFormat(
"EEE MMM dd HH:mm:ss z yyyy");
TimeZone obj = TimeZone.getTimeZone("MST");
formatter.setTimeZone(obj);
MSTTime = formatter.format(currentdate.getTime());

The SQL query is (This value is from my log)

INSERT INTO table_name (rpt_id, rpt_rqstd_dt )
VALUES (111, TO_TIMESTAMP_TZ('Tue Feb 12 06:37:03 MST 2013','DY MON DD HH24:MI:SS TZD YYYY'))

The date value column in database is inserted with value 2/12/2013 7:37:03 AM (Extra 1 hr). This is happening when application is trying to insert a record in Database. If i am running this query manually in the DB it is inserting as 2/12/2013 6:37:03 AM.

The datatype of rpt_rqstd_dt is DATE in oracle DB.


To sink up some view report issue we want to store date in one timezone format(either CST or MST, here we have choose MST) .Can someone help how to fix this issue?

[Martin Vajsar]

Oracle's DATE cannot hold timezone information. I believe the INSERT command therefore converts the timestamp into local time according to the session's current timezone. My guess is that this timezone is different when you connect to the DB from Java and when you connect using standalone tool. Even if I'm wrong in this regard, you should still avoid implicit conversions. All of them are hidden bugs waiting to bite you.

In this case, you should use TO_DATE instead of TO_TIMESTAMP_TZ to avoid implicit conversion. Even better would be to use PreparedStatement to pass in the date, though in this case you need to pass in a Calendar object initialized to the timezone you want to store the date in (so, in your case, it would be MST). You'd avoid converting the date to text in Java and back to date in the database.

[pas out]

Thank you, Martin!

There is some issue in view report what is my main intention.

As stated below we are inserting a row when a user is requesting for a report. When a user is coming again to see the status of his/her report we are updating another column in the same row . This time the update query is as below.

UPDATE table_name SET status_cd = 'C', rpt_end_dt = TO_TIMESTAMP_TZ('Tue Feb 12 08:15:03 MST 2013 ','DY MON DD HH24:MI:SS TZD YYYY') WHERE rpt_id = 111

This time the DB is updating with value 2/12/2013 8:15:03 AM for rpt_end_dt .(There is no extra 1hr added to the value)

So every time during insertion extra 1 hr is adding to the DATE field.

Can you help how to resolve this conflict?

[Martin Vajsar]

When using TO_TIMESTAMP_TZ with INSERT statement, the value is shifted, while when using it with the UPDATE statement, the value is correct - do I understand it right?

What is the datatype of the rpt_end_dt field?
Is the UPDATE done by the same application as the INSERT?

You need to avoid implicit conversions, as I've already mentioned. If your DB field is DATE, use TO_DATE, if it is a TIMESTAMP WIH TIME ZONE, use TO_TIMESTAMP_TZ. Even better would be to use PreparedStatement, though setting or reading a timestamp with time zone correctly this way can be challenging. It works perfectly with DATE, though.

[pas out]

Hi Martin,

rpt_end_dt datatype is DATE .

Yes, the UPDATE is done by same application and it is updating the rpt_end_dt column as the same value . But in case of INSERT it is adding 1hr (i.e. MST time+1 Hr).

[Martin Vajsar]

I don't have any explanation for that. The most I dare to guess is that the two statements have different time zone set in the DB session upon execution, but I don't know why.

I' pretty sure, though, that using TO_DATE instead of TO_TIMESTAMP_TZ will solve the problem. You'll need to remove the timezone from both format patterns (the one used in Java to convert the date to text, and the one you'll use in the TO_DATE function).