Is java.sql.Timestamp Affected by Timezone When Storing Dates in Oracle?

Is java.sql.Timestamp timezone specific when storing dates in a database like Oracle?

String date = "20121225 10:00:00 Z";
String timeZoneId = "Asia/Calcutta";
TimeZone timeZone = TimeZone.getTimeZone(timeZoneId);
DateFormat dateFormatLocal = new SimpleDateFormat("yyyyMMdd HH:mm:ss z");
java.util.Date parsedDate = dateFormatLocal.parse(date + " " + timeZone.getDisplayName(false, TimeZone.SHORT));

if (timeZone.inDaylightTime(parsedDate)) {
    parsedDate = dateFormatLocal.parse(date + " " + timeZone.getDisplayName(true, TimeZone.SHORT));
}

obj.setTsSchedStartTime(new java.sql.Timestamp(parsedDate.getTime()));

The java.sql.Timestamp class is not inherently timezone specific; however, it reflects the UTC time stored by the Java Date object. When storing timestamps into an Oracle database, understanding how timezones impact the stored value is crucial to prevent confusion.

Timestamp utcTimestamp = OffsetDateTime.parse(dateString + "Z").toInstant().toEpochMilli();
stmt.setTimestamp(11, new java.sql.Timestamp(utcTimestamp));

Causes

• The Date object in Java contains the number of milliseconds since the epoch (January 1, 1970, 00:00:00 GMT) and does not store timezone information itself.
• When you create a java.sql.Timestamp from a Date object, it takes the time as-is without any timezone adjustment.
• If the server timezone and the extracted timezone from the input date (Asia/Calcutta) differ, timezone discrepancies can arise.

Solutions

• Use java.time API introduced in Java 8 (e.g., OffsetDateTime) to handle timezones explicitly when converting to UTC.
• Ensure that you explicitly set and convert times to UTC before storing them in the database.
• Check the timezone settings of both your server and database to ensure they match or are correctly accounted for in your application.

Mistake: Not including timezone information in the Date object.

Solution: Always make sure to handle the timezone explicitly when parsing and converting your date.

Mistake: Assuming that java.sql.Timestamp retains timezone details.

Solution: Understand that Timestamp represents a point in time (in UTC) and does not retain timezone data.