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

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'.

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.

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.

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.

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

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