Show TOC Start of Content Area

Background documentation Semantics of the Data Types DATE, TIME, and TIMESTAMP  Locate the document in its SAP Library structure

The JDBC standard defines the data types DATE, TIME, and TIMESTAMP, but it does not define the time zone information for these data types. In addition, it is left to each implementation to decide whether time zone information is stored or not.

Open SQL/JDBC solves this issue by providing users with uniform semantics for time types across all database platforms.

Data Type TIMESTAMP

TIMESTAMP in Open SQL represents a UTC time stamp with time zone information, which determines a point in time world-wide. This means that data of the type TIMESTAMP is stored in the database in the UTC time zone. Time stamp values in the database are therefore independent of the time zone of the application that generated the value, and independent of the database instance. This means that you can compare time stamp values across different time zones.

TIMESTAMP data is represented as an object by using the class java.sql.Timestamp.

Data is fetched from the database by ResultSet.getTimestamp and sent to the database by PreparedStatement.setTimestamp.

A new java.sql.Timestamp object can be generated by using its constructor java.sql.Timestamp.Timestamp(int ms). The argument is the number of seconds * 1000 between the point in time and the epoch (01.01.1970 00:00:00.000 UTC). Only TIMESTAMP data with a precision of 0.01 seconds or less is supported by all database platforms.

This value can be received again by using the method Timestamp.getTime.

Example

import java.sql.SQLException;

 

long now = System.currentTimeMillis()

 

java.sql.Timestamp timestamp = new java.sql.Timestamp(now);

 

PreparedStatement stmt =

   conn.prepareStatement("insert into RESERVATION "

                  + "(ID,  RESERVATION_TIMESTAMP)"

                  + "values (23, ?)");

try {

   stmt.setTimestamp(1, timestamp);

   stmt.executeUpdate();

} finally {

   stmt.close();

}

Data Type TIME

As opposed to TIMESTAMP, the meaning of the JDBC type TIME in Open SQL is a time without a time zone. This type is intended for saving definite times such as 12 mid-day.

TIME data is represented as an object by using the class java.sql.Time.

Data is fetched from the database by ResultSet.getTime and sent to the database by PreparedStatement.setTime.

A new java.sql.Time object can be created using the static method java.sql.Time.valueOf(String jdbcEncodedTime). The string jdbcEncodedString must have the format "hh:mm:ss". Only this static method guarantees that the generated java.sql.Time object is correctly normalized according to the JDBC standard. (A java.sql.Time argument must be normalized according to the JDBC standard in such a way that its date parts produce the value 01.01.1970.)

The time that is represented by a java.sql.Time object can be fetched by the method java.sql.Time.toString as a String with the format "hh:mm:ss".

Caution

java.sql.Time implements the Comparable interface but it violates the interface’s contract. The contract of java.lang.Object.equals is also violated by java.sql.Time. Therefore, do not use the methods java.sql.Time.compareTo and java.sql.Time.equals. The object methods equals and compareTo in java.sql.Dateand java.sql.Time will yield erroneous results if compared objects are constructed in different time zones.

Data Type DATE

The JDBC data type DATE in Open SQL is without a time zone. This data type is intended for saving appointments such as weddings and birthdays across different time zones.

DATE data is represented as an object by using the class java.sql.Date.

Data is fetched from the database by ResultSet.getDate and sent to the database by PreparedStatement.setDate.

A new java.sql.Date object can be created using the static method java.sql.Date.valueOf(String jdbcEncodedDate). The string jdbcEncodedDate must have the format "YYYY-MM-DD". Only this static method guarantees that the generated java.sql.Date object is correctly normalized according to the JDBC standard. (A java.sql.Date argument must be normalized according to the JDBC standard in such a way that its time parts produce the value 00:00:00.)

The java.sql.Date argument must be normalized according to the JDBC standard in such a way that its time parts in reference to the local time zone produce the value 00:00:00.000.

The date that is represented by a java.sql.Date object can be fetched by the method java.sql.Date.toString as a string with the format "yyyy-mm-dd".

Caution

java.sql.Date implements the Comparable interface but it violates the interface’s contract. The contract of java.lang.Object.equals is also violated by java.sql.Date. Therefore, do not use the methods java.sql.Date.compareTo and java.sql.Date.equals. The object methods equals and compareTo in java.sql.Dateand java.sql.Time will yield erroneous results if compared objects are constructed in different time zones.

Example

import java.sql.SQLException;

 

String dateString = "2003-03-24";

 

java.sql.Date date = java.sql.Date.valueOf(dateString);

 

PreparedStatement stmt =

   conn.prepareStatement("insert into RESERVATION "

                  + "(ID,  RESERVATION_DATE)"

                  + "values (23, ?)");

try {

   stmt.setDate(1, date);

   stmt.executeUpdate();

} finally {

   stmt.close();

}

Note

If you need to construct a java.sql.Date and java.sql.Timevalues from a UTC millisecond value, you can use the helper class com.sap.sql.DateTimeNormalizer, which has helper methods for this task.

Example

You need to calculate the java.sql.Date value representing “today” for a given UTC millisecond value.

long utcMillisRepresentingNow = System.currentTimeMillis();

long normalizedMillis = com.sap.sql.DateTimeNormalizer.normalizeSqlDateMillies(utcMillisRepresentingNow);

java.sql.Date today = new java.sql.Date(normalizedMillis);

 

End of Content Area