Java TimeZone ++: mapping Calendar to Oracle Date or TimeStamp

zz Java TimeZone ++: mapping Calendar to Oracle Date or TimeStamp

 

 

 

(original article : http://blog.sarathonline.com/2009/01/java-timezone-mapping-calendar-to.html )

In Oracle Database date and timestamp columns can be read in java using s[g]etTimeStamp() methods for storing date and time information. However, they do not save the TimeZone information! The read and write operations are done on java.sql.TimeStamp which is a subclass of java.util.Date. When reading and writing to database, they just write out the *face value* in Java VM's default TimeZone the code is running on. And read the value too 'TO' the java VM's Default TimeZone. So assuming you WriteDate program on a VM in EST and ReadDate program on another VM in PST (or just change system timezone) - You are getting a different *value* in the date Object.

So, How do you tackle this issue. The best way is to set your application to run on a specified (constant) default timezone. As mentioned in the earlier post this can be achieved by TimeZone.setDefault(). If you are unable to do it, JDBC specification allows sql dates to be read and written using a specified calendar. The resultSet.s[g]etTimeStamp method has an overloaded cousin, that takes a Calendar parameter. These methods save the date *face value* into the db after converting it to the timezone of the calendar passed as argument. So if there is a central place, You would want to have code some thing like this.

staticfinalCalendar networkCal =Calendar.getInstance(TimeZone.getTimeZone("EST"));//Writing somewherePreparedStatement ps = conn.prepareStatement("Update datex set dt = ?, tms = ?, faceval =? where id=1");
 ps.setDate(1,new java.sql.Date(userDate.getTime()));
 ps.setTimestamp(2,newTimestamp(userDate.getTime()),networkCal);//Reading ElsewhereResultSet rs = st.executeQuery("select * from datex where id = 1");
 sqlTS = rs.getTimestamp("tms",networkCal);

In iBatis (we use iBatis), You can have a TypeHandlerCallback for Calender-TimeStamp mapping

classCalendarTypeHandlerCallbackimplementsTypeHandlerCallback{privatestaticfinalCalendar netWorkCal =Calendar.getInstance(TimeZone.getTimeZone("EST"));publicObject getResult(ResultGetter getter)throwsSQLException{Date date = getter.getDate(netWorkCal);Calendar calendar =null;if(date !=null){
            calendar =Calendar.getInstance();
            calendar.setTime(date);}return calendar;}publicvoid setParameter(ParameterSetter setter,Object parameter)throwsSQLException{GregorianCalendar calendar =(GregorianCalendar) parameter;
        java.sql.Date date =new java.sql.Date(calendar.getTimeInMillis());
        setter.setDate(date, netWorkCal);}publicObject valueOf(String s){return s;}}

A Sample jdbc test case is:

/**
 *  Table used:
  CREATE TABLE  "DATEX" 
   (	"ID" NUMBER(3,0) NOT NULL ENABLE, 
	"DT" DATE, 
	"TMS" DATE NOT NULL ENABLE, 
	"FACEVAL" VARCHAR2(50) NOT NULL ENABLE, 
	 CONSTRAINT "DATEX_PK" PRIMARY KEY ("ID") ENABLE
   )

 * 
 * @author spandurangi
 *
 */
public class DBTimeStampReadWriteTest extends TestCase {

	TimeZone userTz = TimeZone.getTimeZone("IST");

	//Used for writing TO and reading FROM RDBMS
	static final Calendar networkCal = Calendar.getInstance(TimeZone.getTimeZone("EST"));
	String userEntered = "2009-01-31 01:00:01";

	/**
	 * @throws Exception
	 */
	public void testDirectWriteConvToSTDTz() throws Exception {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		sdf.setTimeZone(userTz);
		Date userDate = sdf.parse(userEntered);

		System.out.println("========== DATABASE testDirectWriteConvToSTDTz TEST =================");
		Connection conn = null;
		try {
			conn = getConnection();
			PreparedStatement ps = conn.prepareStatement("Update datex set dt = ?, tms = ?, faceval =? where id=1");
			ps.setDate(1, new java.sql.Date(userDate.getTime()));
			ps.setTimestamp(2, new Timestamp(userDate.getTime()), networkCal);
			ps.setString(3, userEntered + " p/w " + sdf.getTimeZone().getID());
			ps.execute();

			Statement st = conn.createStatement();
			String SELECT_LATEST = "select * from datex where id = 1";
			Calendar ret = printRow(st.executeQuery(SELECT_LATEST));
			assertEquals(userDate,ret.getTime());

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}


	/**
	 * Run this test seperately with Vm's TZ same after doing the first one
	 * @throws Exception
	 */
	public void testDirectReadConvToSTDTz() throws Exception {
		TimeZone.setDefault(TimeZone.getTimeZone("PST"));
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
		// to compare the same value!!
		sdf.setTimeZone(userTz);
		Date userDate = sdf.parse(userEntered);

		System.out.println("========== DATABASE testDirectReadConvToSTDTz TEST =================");
		Connection conn = null;
		try {
			conn = getConnection();
			Statement st = conn.createStatement();
			String SELECT_LATEST = "select * from datex where id = 1";
			Calendar ret = printRow(st.executeQuery(SELECT_LATEST));
			assertEquals(userDate,ret.getTime());
			//Comparision may run on any vm!
			Calendar org = Calendar.getInstance();
			org.setTime(userDate);
			ret.setTimeZone(org.getTimeZone());
			System.out.println(decorate(org) + " == " + decorate(ret));
			assertEquals(decorate(org), decorate(ret));
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (conn != null) {
				conn.close();
			}
		}
	}



	private String decorate(Calendar cal) {

		FastDateFormat f = FastDateFormat.getInstance("EEE MMM dd HH:mm:ss z yyyy");
		return f.format(cal);
	}

	private Connection getConnection() throws Exception {
		Class.forName("oracle.jdbc.OracleDriver");
		String url = "jdbc:oracle:thin:@localhost:1521:xe";

		return DriverManager.getConnection(url, "sarath", "pass");
	}

	private Calendar printRow(ResultSet rs) throws Exception {
		rs.next();
		// JDBC driver reads by face value, loses date info
		java.sql.Date sqlDate = rs.getDate("dt");
		Calendar cal = Calendar.getInstance();
		cal.setTime(sqlDate);
		System.out.println("Date Information");
		System.out.println(sqlDate + "::" + sqlDate.getTime() + " :: " + cal.getTime() + " :: " + cal.getTimeInMillis()
				+ " :: " + decorate(cal));

		// JDBC driver reads by face value, applies system tz, (mutates mill
		// secs) (WRONG WAY! binding to VM)
		Timestamp sqlTS = rs.getTimestamp("tms");
		cal = Calendar.getInstance();
		cal.setTime(sqlTS);
		System.out.println("Timestamp Information from db as it is");
		System.out.println(sqlTS + "::" + sqlDate.getTime() + " :: " + cal.getTime() + " :: " + cal.getTimeInMillis()
				+ " :: " + decorate(cal));

		// JDBC driver reads by face value, applies the supplied tz that is
		// supplied (mutates mill secs) (CORRECT! reads in same Tz)
		sqlTS = rs.getTimestamp("tms", networkCal);
		cal.setTime(sqlTS);
		System.out.println("Timestamp Information read with a different Cal (allTimesInTz)");
		System.out.println(sqlTS + "::" + sqlDate.getTime() + " :: " + cal.getTime() + " :: " + cal.getTimeInMillis()
				+ " :: " + decorate(cal));
		return cal;

	}

}

 

If you are using Hibernate, You could use a user type to get similar effect.

Adventurously, I used the TimeZone.setDefault method on non-production systems of an application involving Tomcat, WebLogic a couple of wars and one ear. So far, I have not observed any issues, Google doesnot have any pages that give any known situations either. If I come to know, I will update.