Date/Time Columns and Values
The Ingres DBMS uses the time zone and date format of the client to perform various types of processing of date values. By default, the JDBC Driver uses the Java/JDBC conventions for dates by setting the client time zone to GMT and the date format to match that specified by JDBC. When using these settings, the JDBC Driver manipulates date/time values to match the requirements of both the DBMS and JDBC.
Because the DBMS does not have the actual client time zone, the following restrictions exist:
• Ingres date literal formats are not supported. JDBC specifies the format for date, time, and timestamp literals using the following escape clause syntax:
Literal Syntax
date {d 'yyyy-mm-dd'}
time {t 'hh:mm:ss'}
timestamp {ts 'yyyy-mm-dd hh:mm:ss.f...'}
• These escape clauses must be used to include date, time, and timestamp literals in SQL text. Applications can use other date/time formats by using the classes java.sql.Date, java.sql.Time, java.sql.Timestamp, and java.util.date with an appropriately configured date formatter (java.text.DateFormat).
• Ingres specific date processing, such as intervals and date functions, causes problems associated with the difference between GMT and the actual client time zone and must be avoided.
The JDBC Driver allows the Ingres time zone and date format to be passed to the DBMS. For more information, see
JDBC Driver Properties (see page
JDBC Driver Properties). When these property values are provided, all Ingres date processing is supported in addition to the JDBC functionality listed above.
Note: The Ingres time zone provided must correspond to the Java client default time zone. Using an arbitrary time zone results in time values that differ by the relative time zone offsets.
The JDBC Driver supports Ingres empty dates ('') by returning the JDBC date/time epoch values ('1970-01-01','00:00:00') for methods getDate(), getTime() and getTimestamp() and a zero-length string for getString(). In addition, a DataTruncation warning is created by the driver when an empty date is returned by any of these methods. An application checks for the warning by calling the getWarnings() method after calling one of the previously mentioned methods. An Ingres empty date is different than a NULL value, and cannot be detected using the wasNull() method.
A DataTruncation warning is also created for Ingres date-only values (no time component) for the same conditions described for empty dates. While an Ingres date-only value is comparable to a JDBC DATE value, Ingres date columns are described as being JDBC TIMESTAMP types and date-only values are technically a truncation of that type.
The driver can also be configured to return an alternate value for Ingres empty dates. The system property ingres.jdbc.date.empty can be set to a standard JDBC format date/time value to be returned in place of empty date values. This property can also be set to null to have empty dates treated as null values. A setting of default or empty results in the behavior described above. Input parameter values are also compared to the empty date alternate value when configured to send ingresdate values. An input parameter that matches the configured alternate empty date value results in an empty ingresdate, rather than alternate value, sent to the DBMS. Since null parameters are converted to empty ingresdate values if the system property is configured to null, the null setting should not be used if actual null values need to be inserted into the database.
Ingres interval values are not supported by the methods getDate(), getTime(), and getTimestamp(). An exception is thrown if an Ingres date column containing an interval value is accessed using these methods. Ingres interval values can be retrieved using the getString() method. Because the output of getString() for an interval value is not in a standard JDBC date/time format, the JDBC Driver creates a warning that can be checked by calling the getWarnings() method following the call to getString().