Update and retrieval of timestamps with time zone information in JDBC applications
The JDBC methods and data types that you use and the information that the IBM® Data Server Driver for JDBC and SQLJ has about the column data types determine the timestamp values that are sent to and received from TIMESTAMP WITH TIME ZONE or TIMESTAMP columns.
Updates of values in TIMESTAMP or TIMESTAMP WITH TIME ZONE columns
You can use the following standard JDBC methods to update a TIMESTAMP WITH TIME ZONE or TIMESTAMP column:
- PreparedStatement.setObject
- PreparedStatement.setTimestamp
- PreparedStatement.setString
For a PreparedStatement.setTimestamp call in which the second parameter is a DBTimestamp object and the third parameter is a Calendar object, the value that is passed to a TIMESTAMP WITH TIME ZONE or TIMESTAMP column contains the time zone value in the Calendar parameter, and not the time zone value in the DBTimestamp object. For a PreparedStatement.setTimestamp in which the second parameter is a DBTimestamp object and there is no Calendar parameter, the IBM Data Server Driver for JDBC and SQLJ value that is passed to a TIMESTAMP WITH TIME ZONE or TIMESTAMP column has the default time zone, which is that of the Java virtual machine in which the application is running.
If you want the value that is passed to a TIMESTAMP WITH TIME ZONE or TIMESTAMP column to use the time zone that is in the DBTimestamp object, you need to use PreparedStatement.setObject.
Example: Suppose that table TSTABLE is defined like this:
CREATE TABLE TSTABLE (
TSCOL TIMESTAMP,
TSTZCOL TIMESTAMP WITH TIME ZONE)Also suppose that the default time zone of the Java Virtual Machine (JVM) is UTC-08:00 (Pacific Standard Time). The following code assigns timestamp values to the column.
…
java.util.TimeZone esttz = java.util.TimeZone.getTimeZone("EST");
java.util.Calendar estcal = java.util.Calendar.getInstance(esttz);
// Construct a Calendar object with the
// UTC-05:00 (Eastern Standard Time) time zone.
java.util.Calendar defcal = java.util.Calendar.getInstance();
// Construct a Calendar object
// with the default time zone.
java.sql.Timestamp ts =
java.sql.Timestamp.valueOf("2010-10-27 21:22:33.123456");
// Assign a timestamp to a Timestamp object.
DBTimestamp dbts = new DBTimestamp(ts,estcal);
// Construct a DBTimestamp object that has
// the UTC-05:00 time zone.
…
PreparedStatement ps = con.prepareStatement(
"INSERT INTO TSTABLE (TSCOL,TSTZCOL) VALUES (?,?)");
//
// Use setTimestamp methods to assign a timestamp value to a
// TIMESTAMP WITH TIME ZONE or TIMESTAMP column
//
ps.setTimestamp(1, ts); // Assign a timestamp value in a Timestamp
// object to a TIMESTAMP column.
ps.setTimestamp(2,ts); // Assign the same timestamp value to
// a TIMESTAMP WITH TIME ZONE column.
ps.execute(); // 2010-10-27-21.22.33.123456 is assigned to TSCOL
// if the driver has information that the column
// has the TIMESTAMP data type.
// 2010-10-27-21.22.33.123456-08:00 is assigned to TSCOL
// if the driver has no information about the column
// data type.
// 2010-10-27-21.22.33.123456-08:00 is assigned
// to TSTZCOL regardless of whether the driver
// has information that the the column has
// the TIMESTAMP WITH TIME ZONE data type.
ps.setTimestamp(1, dbts);
// Assign a timestamp value in a DBTimestamp
// object to a TIMESTAMP column.
ps.setTimestamp(2,dbts);
// Assign the same timestamp value to
// a TIMESTAMP WITH TIME ZONE column.
ps.execute(); // 2010-10-27-21.22.33.123456 is assigned to TSCOL
// if the driver has information that the column
// has the TIMESTAMP data type.
// 2010-10-27-21.22.33.123456-08:00 is assigned to TSCOL
// if the driver has no information about the column
// data type.
// 2010-02-27-21.22.33.123456-08:00 is assigned
// to TSTZCOL regardless of whether the driver
// has information that the the column has
// the TIMESTAMP WITH TIME ZONE data type. The
// default time zone of UTC-08:00 is sent to
// the column.
ps.setTimestamp(1, ts, estcal);
// Assign a timestamp value in a Timestamp
// object to a TIMESTAMP column. Include
// a Calendar parameter that specifies
// the UTC-05:00 time zone.
ps.setTimestamp(2, ts, estcal);
// Assign the same timestamp value to
// a TIMESTAMP WITH TIME ZONE column. Include
// a Calendar parameter that specifies the
// UTC-05:00 time zone.
ps.execute(); // 2010-10-28-00.22.33.123456 is assigned to TSCOL
// if the driver has information that the column
// has the TIMESTAMP data type.
// The value is adjusted for the difference
// between the time zone in the Calendar parameter and
// the default time zone.
// 2010-10-28-00.22.33.123456-05:00 is assigned to TSCOL
// if the driver has no information about the column
// data type. The value is adjusted for the difference
// between the time zone in the Calendar parameter and
// the default time zone.
// 2010-10-28-00.22.33.123456-05:00 is assigned
// to TSTZCOL regardless of whether the driver
// has information that the the column has
// the TIMESTAMP WITH TIME ZONE data type.
// The value is adjusted for the difference
// between the time zone in the Calendar parameter and
// the default time zone.
ps.setTimestamp(1, dbts, estcal);
// Assign a timestamp value in a DBTimestamp
// object to a TIMESTAMP column. Include
// a Calendar parameter that specifies the
// UTC-05:00 time zone.
ps.setTimestamp(2, dbts, estcal);
// Assign the same timestamp value to
// a TIMESTAMP WITH TIME ZONE column.
ps.execute(); // 2010-10-28-00.22.33.123456 is assigned to TSCOL
// if the driver has information that the column
// has the TIMESTAMP data type.
// The value is adjusted for the difference
// between the time zone in the Calendar parameter and
// the default time zone.
// 2010-10-28-00.22.33.123456-05:00 is assigned to TSCOL
// if the driver has no information about the column
// data type.
// The value is adjusted for the difference
// between the time zone in the Calendar parameter and
// the default time zone.
// 2010-10-28-00.22.33.123456-05:00 is assigned
// to TSTZCOL regardless of whether the driver
// has information that the the column has
// the TIMESTAMP WITH TIME ZONE data type. The
// time zone in the Calendar parameter, UTC-05:00,
// is sent to the column.
// The value is adjusted for the difference
// between the time zone in the Calendar parameter and
// the default time zone.
ps.setTimestamp(1, ts, defcal);
// Assign a timestamp value in a Timestamp
// object to a TIMESTAMP column. Include
// a Calendar parameter that specifies
// the default time zone (UTC-08:00).
ps.setTimestamp(2, ts, defcal);
// Assign the same timestamp value to
// a TIMESTAMP WITH TIME ZONE column. Include
// a Calendar parameter that specifies the
// default (UTC-08:00) time zone.
ps.execute(); // 2010-10-27-21.22.33.123456 is assigned to TSCOL
// if the driver has information that the column
// has the TIMESTAMP data type.
// 2010-10-27-21.22.33.123456-08:00 is assigned to TSCOL
// if the driver has no information about the column
// data type.
// 2010-10-27-21.22.33.123456-08:00 is assigned
// to TSTZCOL regardless of whether the driver
// has information that the the column has
// the TIMESTAMP WITH TIME ZONE data type.
ps.setTimestamp(1, dbts, defcal);
// Assign a timestamp value in a DBTimestamp
// object to a TIMESTAMP column
ps.setTimestamp(2, dbts, defcal);
// Assign the same timestamp value to
// a TIMESTAMP WITH TIME ZONE column
ps.execute(); // 2010-10-27-21.22.33.123456 is assigned to TSCOL
// if the driver has information that the column
// has the TIMESTAMP data type
// 2010-10-27-21.22.33.123456-08:00 is assigned to TSCOL
// if the driver has no information about the column
// data type
// 2010-10-27-21.22.33.123456-08:00 is assigned
// to TSTZCOL regardless of whether the driver
// has information that the the column has
// the TIMESTAMP WITH TIME ZONE data type. The
// default time zone in the Calendar parameter,
// UTC-08:00, is sent to the column.
//
// Use setObject methods to assign a timestamp value to a
// TIMESTAMP WITH TIME ZONE or TIMESTAMP column
//
ps.setObject(1, ts); // Assign a timestamp value in a Timestamp
// object to a TIMESTAMP column.
ps.setObject(2, ts);
// Assign the same timestamp value to
// a TIMESTAMP WITH TIME ZONE column.
ps.execute(); // 2010-10-27-21.22.33.123456 is assigned to TSCOL
// if the driver has information that the column
// has the TIMESTAMP data type.
// 2010-10-27-21.22.33.123456-08:00 is assigned to TSCOL
// if the driver has no information about the column
// data type. The time zone is the default time zone.
// 2010-10-27-21.22.33.123456-08:00 is assigned
// to TSTZCOL regardless of whether the driver
// has information that the the column has
// the TIMESTAMP WITH TIME ZONE data type. The
// time zone is the default time zone.
ps.setObject(1, dbts);
// Assign a timestamp value in a DBTimestamp
// object to a TIMESTAMP column.
ps.setObject(2, dbts);
// Assign the same timestamp value to
// a TIMESTAMP WITH TIME ZONE column.
ps.execute(); // 2010-10-28-00.22.33.123456 is assigned to TSCOL
// if the driver has information that the column
// has the TIMESTAMP data type.
// 2010-10-28-00.22.33.123456-05:00 is assigned to TSCOL
// if the driver has no information about the column
// data type.
// 2010-10-28-00.22.33.123456-05:00 is assigned
// to TSTZCOL regardless of whether the driver
// has information that the the column has
// the TIMESTAMP WITH TIME ZONE data type.
// The time zone is the time zone in the DBTimestamp
// object.
//
// Use setString methods to assign a timestamp value to a
// TIMESTAMP WITH TIME ZONE or TIMESTAMP column
//
ps.setString(1, "2010-10-27-21.22.33.123456");
// Assign a constant timestamp value
// with no time zone to a TIMESTAMP column.
ps.setString(2, "2010-10-27-21.22.33.123456");
// Assign the same timestamp value to
// a TIMESTAMP WITH TIME ZONE column.
ps.execute(); // 2010-10-27-21.22.33.123456 is assigned to TSCOL
// regardless of whether the driver has information
// that the column has the TIMESTAMP data type.
// 2010-10-27-21.22.33.123456-08:00 is assigned
// to TSTZCOL if the driver has information that
// the column has the TIMESTAMP WITH TIME ZONE
// data type. The time zone is the default time zone.
// 2010-10-27-21.22.33.123456 is assigned to TSTZCOL
// if the driver has no information about the column
// data type.
ps.setString(1, "2010-10-27-21.22.33.123456-05:00");
// Assign a constant timestamp value
// with a time zone to a TIMESTAMP column.
ps.setString(2, "2010-10-27-21.22.33.123456-05:00");
// Assign the same timestamp value to
// a TIMESTAMP WITH TIME ZONE column.
ps.execute(); // 2010-10-27-21.22.33.123456 is assigned to TSCOL
// if the driver has information that the column
// data type is TIMESTAMP.
// 2010-10-27-21.22.33.123456-05:00 is assigned to
// TSCOL if the driver has no information about the
// column data type.
// 2010-10-27-21.22.33.123456-05:00 is assigned
// to TSTZCOL regardless of whether the driver has
// information that the column data type is
// TIMESTAMP WITH TIME ZONE.Alternatively, if you want to assign data that has a time zone or has a precision of greater than nine to a TIMESTAMP WITH TIME ZONE column, you can construct a DBTimestamp object, and use the IBM Data Server Driver for JDBC and SQLJ-only method DB2PreparedStatement.setDBTimestamp to update a TIMESTAMP WITH TIME ZONE column.
Example: Suppose that table TSTABLE is defined like this:
CREATE TABLE TSTABLE (
TSCOL TIMESTAMP,
TSTZCOL TIMESTAMP(12) WITH TIME ZONE)The following code assigns a timestamp value with a time zone and a precision of 10 to each column.
…
DBTimestamp tstz =
DBTimestamp.valueOfDBString("2010-10-28-00.22.33.1234567890-05:00");
// Create a DBTimestamp object from the input value
PreparedStatement ps = con.prepareStatement(
"INSERT INTO TSTABLE (TSCOL, TSTXCOL) VALUES (?,?)");
DB2PreparedStatement dbps = (DB2PreparedStatement)ps;
dbps.setDBTimestamp(1, tstz);
dbps.setDBTimestamp(2, tstz);
dbps.execute(); // 2010-10-28-00.22.33.123456 is assigned to TSCOL if
// the driver has information that the column data type is
// TIMESTAMP.
// 2010-10-28-00.22.33.1234567890-05:00 is assigned to TSCOL
// if the driver has no information about the column
// data type.
// 2010-10-28-00.22.33.1234567890-05:00 is assigned to TSTZCOL
// regardless of whether the driver has information that
// the column data type is TIMESTAMP(12) WITH TIME ZONE.Retrieval of values from TIMESTAMP or TIMESTAMP WITH TIME ZONE columns
You can use the following standard JDBC methods to retrieve data from a TIMESTAMP WITH TIME ZONE or TIMESTAMP column or output parameter:
- ResultSet.getTimestamp
- CallableStatement.getTimestamp
- ResultSet.getObject
- CallableStatement.getObject
- ResultSet.getString
- CallableStatement.getString
For
a ResultSet.getTimestamp, CallableStatement.getTimestamp, ResultSet.getObject,
or CallableStatement.getObject call, you can specify
the type of object that you want the IBM Data Server Driver for JDBC and SQLJ to
return by setting the DB2BaseDataSource.timestampOutputType property:
- If you set the property to DB2BaseDataSource.JDBC_TIMESTAMP (1), the driver returns a java.sql.Timestamp object.
- If you set the property to DB2BaseDataSource.JCC_DBTIMESTAMP (2), the driver returns a com.ibm.db2.jcc.DBTimestamp object.
For a ResultSet.getTimestamp or CallableStatement.getTimestamp call, if the ResultSet.getTimestamp or CallableStatement.getTimestamp call has a Calendar parameter with a non-null value, the IBM Data Server Driver for JDBC and SQLJ uses the Calendar object when it constructs the returned object. If the ResultSet.getTimestamp or CallableStatement.getTimestamp call has no Calendar parameter, or the Calendar parameter value is null, the IBM Data Server Driver for JDBC and SQLJ uses the default time zone when it constructs the returned object.
If you want to retrieve a timestamp with the time zone value that is in a TIMESTAMP WITH TIME ZONE column, call ResultSet.getObject or CallableStatement.getObject, and then call DBTimestamp.toDBString(true) to retrieve the timestamp with the time zone.
getString retrieves the timestamp value in the standard JDBC format: without the time zone, and with a precision of up to nine. The returned value is adjusted for the difference between the time zone of the column value and the default time zone.
Example: Suppose that table TSTABLE is defined like this:
CREATE TABLE TSTABLE (
TSCOL TIMESTAMP,
TSTZCOL TIMESTAMP WITH TIME ZONE)Also suppose that the default time zone is UTC-08:00 (Pacific Standard Time). The following code retrieves timestamp values from the TIMESTAMP column.
…
java.util.TimeZone esttz = java.util.TimeZone.getTimeZone("EST");
java.util.Calendar estcal = java.util.Calendar.getInstance(esttz);
java.util.Calendar defcal = java.util.Calendar.getInstance();
Statement stmt = conn.createStatement ();
ResultSet rs = stmt.executeQuery("SELECT TSCOL, TSTZCOL FROM TSTABLE");
com.ibm.db2.jcc.DB2ResultSet dbrs = (com.ibm.db2.jcc.DB2ResultSet)rs;
Timestamp ts;
DBTimestamp dbts;
…
rs.next();
// Suppose that the TSCOL column value is 2010-10-27-21.22.33.123456
ts=rs.getTimestamp(1); // Retrieve the TIMESTAMP column value
// into a Timestamp object.
ts.toString(); // Format the Timestamp object as a String.
// 2010-10-27-21:22:33.123456 is
// returned.
((DBTimestamp)ts).toDBString(false); // Cast the retrieved object to a
// DBTimestamp object, and format the
// value as a String, without the time
// zone information.
// 2010-10-27-21.22.33.123456 is returned.
((DBTimestamp)ts).toDBString(true); // Cast the retrieved object to a
// DBTimestamp object, and format the value
// as a String, with the time zone
// information.
// 2009-02-27-21.22.33.123456-08:00 is
// returned. The time zone is the default
// time zone.
ts=rs.getTimestamp(1,estcal); // Retrieve the TIMESTAMP column value
// into a Timestamp object. Specify a
// calendar parameter that says that the
// time zone is UTC-05:00.
ts.toString(); // Format the value as a String, using the
// default time zone of UTC-08:00.
// 2010-10-27-18:22:33.123456 is
// returned.
((DBTimestamp)ts).toDBString(false); // Cast the retrieved object to a
// DBTimestamp object, and format the
// value as a String, without the time zone
// information.
// 2010-10-27-21.22.33.123456 is returned.
((DBTimestamp)ts).toDBString(true); // Cast the retrieved object to a
// DBTimestamp object, and format the
// value as a String, with the time zone
// information.
// 2010-10-27-21.22.33.123456-05:00 is
// returned. The time zone is the time zone
// in the Calendar parameter.
ts=rs.getObject(1); // Retrieve the TIMESTAMP column value
// into an Object.
ts.toString(); // Format the Timestamp object as a String.
// 2010-10-27-21:22:33.123456 is
// returned.
((DBTimestamp)ts).toDBString(false); // Cast the retrieved object to a
// DBTimestamp object, and format the
// value as a String, without the time
// zone information.
// 2010-10-27-21.22.33.123456 is returned.
((DBTimestamp)ts).toDBString(true); // Cast the retrieved object to a
// DBTimestamp object, and format the value
// as a String, with the time zone
// information.
// 2009-02-27-21.22.33.123456-08:00 is
// returned. The time zone is the default
// time zone. Alternatively, you can use DB2ResultSet methods to retrieve the TIMESTAMP or TIMESTAMP WITH TIME ZONE column values.
Example: Suppose that table TSTABLE is defined like this:
CREATE TABLE TSTABLE (
TSCOL TIMESTAMP,
TSTZCOL TIMESTAMP(12) WITH TIME ZONE)Also suppose that the default time zone is UTC-08:00 (Pacific Standard Time). The following code retrieves timestamp values from the TIMESTAMP and TIMESTAMP WITH TIME ZONE columns.
…
java.util.TimeZone esttz = java.util.TimeZone.getTimeZone("EST");
java.util.Calendar estcal = java.util.Calendar.getInstance(esttz);
java.util.Calendar defcal = java.util.Calendar.getInstance();
Statement stmt = conn.createStatement ();
ResultSet rs = stmt.executeQuery("SELECT TSCOL, TSTZCOL FROM TSTABLE");
com.ibm.db2.jcc.DB2ResultSet dbrs = (com.ibm.db2.jcc.DB2ResultSet)rs;
Timestamp ts;
DBTimestamp dbts;
…
rs.next();
// Suppose that the TSTZCOL column value is 2010-10-28-00.22.33.123456-05:00, and
// the TSCOL column value is 2010-10-27-21.22.33.123456.
ts=dbrs.getDBTimestamp(1); // Retrieve the TIMESTAMP column value into
// a Timestamp object.
ts.toString(); // Format the Timestamp object as a String.
// 2010-10-27-21:22:33.123456 is
// returned.
((DBTimestamp)ts).toDBString(false); // Format the value as a String, without
// the time zone information.
// 2010-10-27-21.22.33.123456 is returned.
((DBTimestamp)ts).toDBString(true); // Format the value as a String, with the
// time zone information.
// 2009-02-27-21.22.33.123456-08:00 is
// returned. The time zone is the default
// time zone.
ts=dbrs.getDBTimestamp(2); // Retrieve the TIMESTAMP WITH TIME ZONE
// column value into a Timestamp object.
ts.toString(); // Format the Timestamp object as a String.
// 2010-10-27-21:22:33.123456 is
// returned. The returned value differs
// from the original value because toString
// uses the default time zone in its
// calculations.
((DBTimestamp)ts).toDBString(false); // Format the value as a String, without
// the time zone information.
// 2010-10-28-00.22.33.123456 is returned.
((DBTimestamp)ts).toDBString(true); // Format the value as a String, with the
// time zone information.
// 2010-10-28-00.22.33.123456-05:00 is
// returned. The time zone is the time zone
// from the retrieved column value.
dbts = (DBTimestamp)rs.getTimestamp(2);
// Retrieve the TIMESTAMP WITH TIME ZONE
// column value into a DBTimestamp object.
dbts.toString(); // Format the DBTimestamp object as a String.
// 2010-10-27-21:22:33.123456 is
// returned. The value is adjusted for the
// difference between the time zone in the
// column value and the default time zone.
dbts.toDBString(false); // Format the value as a String, without
// the time zone information. The value is
// adjusted for the difference between the
// time zone in the column value and the
// default time zone.
// 2010-10-27-21.22.33.123456 is returned.
dbts.toDBString(true); // Format the value as a String, with the
// time zone information.
// 2009-02-27-21.22.33.123456-08:00 is
// returned. The time zone is the default
// time zone. The value is adjusted for
// the difference between the time zone in
// the column value and the default
// time zone.
dbts = (DBTimestamp)rs.getTimestamp(2, defcal);
// Retrieve the TIMESTAMP WITH TIME ZONE
// column value into a DBTimestamp object,
// using the default Calendar to construct
// the DBTimestamp object.
dbts.toString(); // Format the DBTimestamp object as a String.
// 2010-10-27-21:22:33.123456 is
// returned. The value is adjusted for
// the difference between the time zone in
// the column value and the time zone
// in the Calendar parameter.
dbts.toDBString(false); // Format the value as a String, without
// the time zone information.
// 2010-10-27-21.22.33.123456 is returned.
// The value is adjusted for
// the difference between the time zone in
// the column value and the time zone
// in the Calendar parameter.
dbts.toDBString(true); // Format the value as a String, with the
// time zone information.
// 2009-02-27-21.22.33.123456-08:00 is
// returned. The value is adjusted for
// the difference between the time zone in
// the column value and the time zone
// in the Calendar parameter.
dbts = (DBTimestamp)rs.getObject(2); // Retrieve the TIMESTAMP WITH TIME ZONE
// column value into an Object, and cast
// the object as a DBTimestamp object.
dbts.toString(); // Format the DBTimestamp object as a String.
// 2010-10-27-21:22:33.123456 is
// returned. The returned value differs from
// the original value because toString uses
// the default time zone in its calculations.
dbts.toDBString(false); // Format the value as a String, without
// the time zone information.
// 2010-10-28-00.22.33.123456 is returned.
dbts.toDBString(true); // Format the value as a String, with the
// time zone information.
// 2009-10-28-00.22.33.123456-05:00 is
// returned. The time zone is the time
// zone in the retrieved column value. Recommendation: Use getObject or getDBTimestamp,
followed by setObject or setDBTimestamp when
you need to preserve the original timestamp with time zone information
when you retrieve data from one table and insert it into another table.