Is java.sql.Timestamp timezone specific?

Question

I have to store UTC dateTime in DB.
I have converted the dateTime given in specific timezone to UTC. for that I followed the below code.
My input dateTime is "20121225 10:00:00 Z" timezone is "Asia/Calcutta"
My Server/DB(oracle) is running in the same timezone(IST) "Asia/Calcutta"

Get the Date object in this specific Timezone

        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");
                    //This date object is given time and given timezone
        java.util.Date parsedDate = dateFormatLocal.parse(date + " "  
                         + timeZone.getDisplayName(false, TimeZone.SHORT));

        if (timeZone.inDaylightTime(parsedDate)) {
            // We need to re-parse because we don't know if the date
            // is DST until it is parsed...
            parsedDate = dateFormatLocal.parse(date + " "
                    + timeZone.getDisplayName(true, TimeZone.SHORT));
        }

       //assigning to the java.sql.TimeStamp instace variable
        obj.setTsSchedStartTime(new java.sql.Timestamp(parsedDate.getTime()));

Store into DB

        if (tsSchedStartTime != null) {
            stmt.setTimestamp(11, tsSchedStartTime);
        } else {
            stmt.setNull(11, java.sql.Types.DATE);
        }

OUTPUT

DB (oracle) has stored the same given dateTime: "20121225 10:00:00 not in UTC.

I have confirmed from the below sql.

     select to_char(sched_start_time, 'yyyy/mm/dd hh24:mi:ss') from myTable

My DB server also running on the same timezone "Asia/Calcutta"

It gives me the below appearances

Date.getTime() is not in UTC
Or Timestamp is has timezone impact while storing into DB What am I doing wrong here?

One more question:

Will timeStamp.toString() print in local timezone like java.util.date does? Not UTC?

To answer the question in your title, no, java.sql.Timestamp is based on UTC. Displaying the timestamp is timezone specific. — Gilbert Le Blanc
@Gilbert Le Blanc Thanks!. Could you please answer me "new java.sql.Timestamp(parsedDate.getTime())" will return GMT timestamp object for my input? — Kanagavelu Sugumar
@GilbertLeBlanc Although java.sql.Timestamp is in UTC, when storing in a TIMESTAMP field without timezone information, it uses the equivalent date/time in the current timezone of the virtual machine — Mark Rotteveel
@Kanagavelu Sugumar: Yes. the getTime method of the Date class returns the number of milliseconds since January 1, 1970, 00:00:00 GMT. — Gilbert Le Blanc
@MarkRotteveel Mark I didn't get your point "storing in a TIMESTAMP field without timezone information, it uses the equivalent date/time in the current timezone of the virtual machine". Could you please elaborate in your answer. It will be more helpful to me. — Kanagavelu Sugumar

Mark Rotteveel Mark Rotteveel · Accepted Answer · 2012-12-28T14:11:02

Although it is not explicitly specified for setTimestamp(int parameterIndex, Timestamp x) drivers have to follow the rules established by the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) javadoc:

Sets the designated parameter to the given java.sql.Timestamp value, using the given Calendar object. The driver uses the Calendar object to construct an SQL TIMESTAMP value, which the driver then sends to the database. With a Calendar object, the driver can calculate the timestamp taking into account a custom time zone. If no Calendar object is specified, the driver uses the default time zone, which is that of the virtual machine running the application.

When you call with setTimestamp(int parameterIndex, Timestamp x) the JDBC driver uses the time zone of the virtual machine to calculate the date and time of the timestamp in that time zone. This date and time is what is stored in the database, and if the database column does not store time zone information, then any information about the zone is lost (which means it is up to the application(s) using the database to use the same time zone consistently or come up with another scheme to discern timezone (ie store in a separate column).

For example: Your local time zone is GMT+2. You store "2012-12-25 10:00:00 UTC". The actual value stored in the database is "2012-12-25 12:00:00". You retrieve it again: you get it back again as "2012-12-25 10:00:00 UTC" (but only if you retrieve it using getTimestamp(..)), but when another application accesses the database in time zone GMT+0, it will retrieve the timestamp as "2012-12-25 12:00:00 UTC".

If you want to store it in a different timezone, then you need to use the setTimestamp(int parameterIndex, Timestamp x, Calendar cal) with a Calendar instance in the required timezone. Just make sure you also use the equivalent getter with the same time zone when retrieving values (if you use a TIMESTAMP without timezone information in your database).

So, assuming you want to store the actual GMT timezone, you need to use:

Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
stmt.setTimestamp(11, tsSchedStartTime, cal);

With JDBC 4.2 a compliant driver should support java.time.LocalDateTime (and java.time.LocalTime) for TIMESTAMP (and TIME) through get/set/updateObject. The java.time.Local* classes are without time zones, so no conversion needs to be applied (although that might open a new set of problems if your code did assume a specific time zone).

Is java.sql.Timestamp timezone specific?

7 Answers