Date, time, and timestamp values that can cause problems in JDBC and SQLJ applications

You might receive unexpected results in JDBC and SQLJ applications if you use date, time, and timestamp in certain situations.

The following items might cause problems:

Use of the hour '24' to represent midnight.
Use of a date between October 5, 1582, and October 14, 1582, inclusive.
The system on which the data server resides adjusts for daylight saving time.

Problems with using the hour '24' as midnight

The IBM® Data Server Driver for JDBC and SQLJ uses Java™ data types for its internal processing of input and output parameters and ResultSet content in JDBC and SQLJ applications. The Java data type that is used by the driver is based on the best match for the corresponding SQL type when the target SQL type is known to the driver.

For values that are assigned to or retrieved from DATE, TIME, or TIMESTAMP SQL types, the IBM Data Server Driver for JDBC and SQLJ uses java.sql.Date for DATE SQL types, java.sql.Time for TIME SQL types, and java.sql.Timestamp for TIMESTAMP SQL types.

When you assign a string value to a DATE, TIME, or TIMESTAMP target, the IBM Data Server Driver for JDBC and SQLJ uses Java facilities to convert the string value to a java.sql.Date, java.sql.Time, or java.sql.Timestamp value. If a string representation of a date, time, or timestamp value does not correspond to a real date or time, Java adjusts the value to a real date or time value. In particular, Java adjusts an hour value of '24' to '00' of the next day. This adjustment can result in an exception for a timestamp value of '9999-12-31 24:00:00.0', because the adjusted year value becomes '10000'.

Important: To avoid unexpected results when you assign or retrieve date, time, or timestamp values in JDBC or SQLJ applications, ensure that the values are real date, time, or timestamp values. In addition, do not use '24' as the hour component of a time or timestamp value.

If a value that does not correspond to a real date or time, such as a value with an hour component of '24', is stored in a TIME or TIMESTAMP column, you can avoid adjustment during retrieval by executing the SQL CHAR function against that column in the SELECT statement that defines a ResultSet. Executing the CHAR function converts the date or time value to a character string value on the database side. However, if you use the getTime or getTimestamp method to retrieve that value from the ResultSet, the IBM Data Server Driver for JDBC and SQLJ converts the value to a java.sql.Time or java.sql.Timestamp type, and Java adjusts the value. To avoid date adjustment, execute the CHAR function against the column value, and retrieve the value from the ResultSet with the getString method.

The following examples show the results of updating DATE, TIME, or TIMESTAMP columns in JDBC or SQLJ applications, when the application data does not represent real dates or times.

Table 1. Examples of updating DATE, TIME, or TIMESTAMP SQL values with Java date, time, or timestamp values that do not represent real dates or times
String input value	Target type in database	Value sent to table column, or exception
2008-13-35	DATE	2009-02-04
25:00:00	TIME	01:00:00
24:00:00	TIME	00:00:00
2008-15-36 28:63:74.0	TIMESTAMP	2009-04-06 05:04:14.0
9999-12-31 24:00:00.0	TIMESTAMP	Exception, because the adjusted value (10000-01-01 00:00:00.0) exceeds the maximum year of 9999.

The following examples demonstrate the results of retrieving data from TIMESTAMP columns in JDBC or SQLJ applications, when the values in those columns do not represent real dates or times.

Table 2. Results of retrieving DATE, TIME, or TIMESTAMP SQL values that do not represent real dates or times into Java application variables
SELECT statement	Value in TIMESTAMP column TS_COL	Target type in application (getXXX method for retrieval)	Value retrieved from table column
SELECT TS_COL FROM TABLE1	2000-01-01 24:00:00.000000	java.sql.Timestamp (getTimestamp)	2000-01-02 00:00:00.000000
SELECT TS_COL FROM TABLE1	2000-01-01 24:00:00.000000	String (getString)	2000-01-02 00:00:00.000000
SELECT CHAR(TS_COL) FROM TABLE1	2000-01-01 24:00:00.000000	java.sql.Timestamp (getTimestamp)	2000-01-02 00:00:00.000000
SELECT CHAR(TS_COL) FROM TABLE1	2000-01-01 24:00:00.000000	String (getString)	2000-01-01 24:00:00.000000 (no adjustment by Java)

Problems with using dates in the range October 5, 1582, through October 14, 1582

The Java java.util.Date and java.util.Timestamp classes use the Julian calendar for dates before October 4, 1582, and the Gregorian calendar for dates starting with October 4, 1582. In the Gregorian calendar, October 4, 1582, is followed by October 15, 1582. If a Java program encounters a java.util.Date or java.util.Timestamp value that is between October 5, 1582, and October 14, 1582, inclusive, Java adds 10 days to that date. Therefore, a DATE or TIMESTAMP value in a Db2® table that has a value between October 5, 1582, and October 14, 1582, inclusive, is retrieved in a Java program as a java.util.Date or java.util.Timestamp value between October 15, 1582, and October 24, 1582, inclusive. A java.util.Date or java.util.Timestamp value in a Java program that is between October 5, 1582, and October 14, 1582, inclusive, is stored in a Db2 table as a DATE or TIMESTAMP value between October 15, 1582, and October 24, 1582, inclusive.

Example: Retrieve October 10, 1582, from a DATE column.

// DATETABLE has one date column with one row.
// Its value is 1582-10-10.
java.sql.ResultSet rs = 
 statement.executeQuery(select * from DATETABLE);
rs.next();
System.out.println(rs.getDate(1)); // Value is retrieved as 1582-10-20

Example: Store October 10, 1582, in a DATE column.

java.sql.Date d = java.sql.Date.valueOf("1582-10-10");
java.sql.PreparedStatement ps = 
 c.prepareStatement("Insert into DATETABLE values(?)");
ps.setDate(1, d);
ps.executeUpdate(); // Value is inserted as 1582-10-20

To retrieve a value in the range October 5, 1582, to October 14, 1582, from a Db2 table without date adjustment, execute the SQL CHAR function against the DATE or TIMESTAMP column in the SELECT statement that defines a ResultSet. Executing the CHAR function converts the date or time value to a character string value on the database side.

To store a value in the range October 5, 1582, to October 14, 1582 in a Db2 table without date adjustment, you can use one of the following techniques:

For a JDBC or an SQLJ application, use the setString method to assign the value to a String input parameter. Cast the input parameter as VARCHAR, and execute the DATE or TIMESTAMP function against the result of the cast. Then store the result of the DATE or TIMESTAMP function in the DATE or TIMESTAMP column.
For a JDBC application, set the Connection or DataSource property sendDataAsIs to true, and use the setString method to assign the date or timestamp value to the input parameter. Then execute an SQL statement to assign the String value to the DATE or TIMESTAMP column.

Example: Retrieve October 10, 1582, from a DATE column without date adjustment.

// DATETABLE has one date column called DATECOL with one row.
// Its value is 1582-10-10.
java.sql.ResultSet rs = 
 statement.executeQuery(SELECT CHAR(DATECOL) FROM DATETABLE);
rs.next();
System.out.println(rs.getString(1)); // Value is retrieved as 1582-10-10

Example: Store October 10, 1582, in a DATE column without date adjustment.

String s = "1582-10-10";
java.sql.Statement stmt = c.createStatement;
java.sql.PreparedStatement ps = 
 c.prepareStatement("Insert INTO DATETABLE VALUES " +
  "(DATE(CAST (? AS VARCHAR)))");
ps.setString(1, s);
ps.executeUpdate(); // Value is inserted as 1582-10-10

To avoid date adjustment, set the SQLJ option sqljAvoidTimeStampConversion to true. The SQLJ option sqljAvoidTimeStampConversion=true returns the timestamp data in CHAR format.

Example: Retrieve October 12, 1582, from a DATE column without date adjustment.

import java.sql.*;
import javax.sql.DataSource;
import sqlj.runtime.ref.DefaultContext;
import com.ibm.db2.jcc.DBTimestamp;
import com.ibm.db2.jcc.DB2BaseDataSource;
#sql context DBContext;

public class UpdateTest 
{
// Set the timestamp conversion off.
static boolean SqljAvoidTimeStampConversion =true;
static DefaultContext ctx = null;
java.sql.Connection con;
 public static void main(String[] args) throws Exception
  { 
   javax.sql.DataSource ds = new com.ibm.db2.jcc.DB2SimpleDataSource(); 
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setServerName 
                                             ("myserver.svl.ibm.com");
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setPortNumber(446);
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDatabaseName("MYDB");
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDriverType(4);
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setTimestampOutputType
                                             (DB2BaseDataSource.JCC_DBTIMESTAMP);
     ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setSqljAvoidTimeStampConversion 
                                             (true); 
	   java.sql.Connection con = ds.getConnection("myid", "mypwd");
// Create a connection context instance to pass to the 
// insertAndSelectTimestamp method.
     ctx = new DefaultContext(con); 
	   System.out.println(" Default Context  Obtained Successfully...");
     insertAndSelectTimestamp(ctx);   
     }
   public static  void insertAndSelectTimestamp
           (DefaultContext ctx)throws java.sql.SQLException 
     {         
		 String temp = "";    
      #sql[ctx]  { CREATE TABLE Mytable (C1 TIMESTAMP(12))}; 
// Create the table.						 
      System.out.println ("table created"); 
      #sql[ctx]  { INSERT INTO Mytable (C1) 
                   VALUES('1582-10-12-21.22.33.123456789012') }; 
// Insert a value in the range October 5, 1582 through October 14, 1582.
// sqljAvoidTimeStampConversion is set to true so the value is stored as is.
      System.out.println ("table inserted"); 
      #sql[ctx] { COMMIT};
// Retrieve the value you inserted. sqljAvoidTimeStampConversion = true, 
// so the timestamp should not be adjusted, and you should get back the 
// value that you put in.
      #sql[ctx] { select C1 into :temp from Mytable };
      } 
}

Problems due to time adjustment for daylight saving time

Some locations observe daylight saving time. That means:

The clock moves forward by one hour on a specified date in the spring, which makes the length of that day 23 hours.
The clock moves backward by one hour on a specified date in the fall, which makes the length of that day is 25 hours.

If time adjustment occurs during the hour after the switch to daylight saving time, a timestamp value that is retrieved from a Db2 table in a JDBC or SQLJ program might be incorrect. You can set the sqljAvoidTimeStampConversion property to true to prevent time adjustment. When you set the sqljAvoidTimeStampConversion property to true, you need to use the ResultSet.getString method to retrieve timestamp values in String format.

Example: Retrieve a timestamp value without time adjustment.

import java.io.IOException;
import java.sql.*;
import java.util.Arrays;
import com.ibm.db2.jcc.DB2BaseDataSource;
import java.util.Calendar;
 
public class TestTimeStamp {
 public static void main(String[] args) throws Exception {
  javax.sql.DataSource ds = new com.ibm.db2.jcc.DB2SimpleDataSource();
  ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setServerName
   ("myserver.svl.ibm.com");
  ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setPortNumber(446);
  ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDatabaseName("MYDB");
  ((com.ibm.db2.jcc.DB2BaseDataSource) ds).setDriverType(4);
// Set property setSqljAvoidTimeStampConvertion to true to
// prevent adjustment for daylight saving time during timestamp
// retrieval
  ((com.ibm.db2.jcc.DB2BaseDataSource) 
   ds).setSqljAvoidTimeStampConversion(true);
  java.sql.Connection con = ds.getConnection("myid", "mypwd");          
  System.out.println("Connected to data server");
  Statement stmt1 = con.createStatement();
  try {
// Drop the test table if it already exists; otherwise, do nothing.
        stmt1.execute("DROP TABLE MYTABLE1");
       } catch (Exception e) {
       }
// Create a test table with a TIMESTAMP column and a 
// TIMESTAMP WITH TIME ZONE column.
       stmt1.execute("CREATE TABLE MYTABLE1 " +
        "(TSTAMP1 TIMESTAMP WITH TIME ZONE, " +
        "TSTAMP2 TIMESTAMP)");
// Insert the same timestamp value into the TIMESTAMP WITH 
// TIME ZONE column and the TIMESTAMP column so you can see
// the retrieved timestamp values when time adjustment
// is not performed. The inserted timestamp is 15 minutes
// after daylight saving time begins on March 13, 2022.
       PreparedStatement pst1 = 
        con.prepareStatement("INSERT INTO MYTABLE1 VALUES (?,?)");
       pst1.setString(1, "2022-03-13 02:15:00.00000");
       pst1.setString(2, "2022-03-13 02.15.00.00000");
       pst1.executeUpdate();
// Create a Calendar object and assign the same timestamp
// value that is in the table to the Calendar object.
// That timestamp value has the default time zone and 
// locale for the system on which this program runs.
       Calendar cd = Calendar.getInstance();
       cd.set(2022, Calendar.MARCH, 13, 2, 15, 00);
       System.out.println("Calendar time: " + cd.getTime());
       Statement stmt2 = 
        con.createStatement();
       ResultSet rs2 = stmt2.executeQuery(
	    "SELECT TSTAMP1, TSTAMP2 FROM MYTABLE1");
// Retrieve the TIMESTAMP values using ResultSet.getString
// to allow the driver to prevent daylight saving time 
// adjustment.
       while (rs2.next()) {
        System.out.println("TSTAMP1 (with time zone): " + 
         rs2.getString(1));
        System.out.println("TSTAMP2 (without time zone): " + 
         rs2.getString(2));
       }
 }
}

If you run the program above in Pacific Daylight Time, you receive the following results:

Calendar time: Sun Mar 13 03:15:00 PDT 2022              
TSTAMP1 (with time zone): 2022-03-13 02:15:00.000000-07:00
TSTAMP2 (without time zone): 2022-03-13 02:15:00.000000