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, Dec 28 '12 at 14:06
@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, Dec 28 '12 at 14:23
@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, Dec 28 '12 at 14:25
@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, Dec 28 '12 at 14:50
@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, Dec 28 '12 at 18:42

Mark Rotteveel · Accepted Answer · 2023-08-06T09:31:36.460

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

That is:

replace getDate(..) with getObject(.., LocalDate.class)
replace setDate(.., dateValue) with setObject(.., localDateValue)
replace getTime(..) with getObject(.., LocalTime.class)
replace setTime(.., timeValue) with setObject(.., localTimeValue)
replace getTimestamp(..) with getObject(.., LocalDateTime.class)
replace setTimestamp(.., timestampValue) with setObject(.., localDateTimeValue)

I want to store my time in GMT. I believe my timeStamp in GMT. So what should i do? is it setTimestamp(int parameterIndex, Timestamp tzGmtObj, Calendar calGmtObj) ?? — Kanagavelu Sugumar, Dec 28 '12 at 14:19
if "setTimestamp(int parameterIndex, Timestamp x)" uses the timezone of the virtual machine. what is the role of x in the code ? — Kanagavelu Sugumar, Dec 28 '12 at 14:26
@KanagaveluSugumar That is the actual timestamp value to set. A driver only uses the Calendar object for the timezone information, not for its value! — Mark Rotteveel, Dec 28 '12 at 14:27
oooh Great!!! I will try this in my code. and let you know the result. Thanks a lot. — Kanagavelu Sugumar, Dec 28 '12 at 14:32
Great, It is working :) I spend lot of time to prove (Date/Timestamp).getTime() is holding timezone specific time in milliseconds. Now i understood that It is not. It always give UTC milliseconds irrespective of timezone. But (Date/Timestamp).toString() is timezone specific. Thanks a lot. — Kanagavelu Sugumar, Dec 28 '12 at 18:34
The `toString()` on `java.lang.Date` (and `java.lang.Timestamp`) will always present it in your local timezone. — Mark Rotteveel, Dec 29 '12 at 09:35
Thank you. It saved my butt in trying to figure out why what was being stored in the database was not what was being passed to it. — Trebor, May 21 '14 at 19:50
Yeah very confusing. If it holds no (accessible/formattable) timezone information it should always store itself as UTC. — Splitframe, Mar 28 '22 at 14:35
@Splitframe The recommended approach since Java 8 (JDBC 4.2) is to use `java.time.LocalDateTime` instead (using `getObject(..., LocalDateTime.class)`. — Mark Rotteveel, Mar 28 '22 at 16:56

Evgeniy Dorofeev · Answer 2 · 2015-08-26T04:51:43.537

41

I think the correct answer should be java.sql.Timestamp is NOT timezone specific. Timestamp is a composite of java.util.Date and a separate nanoseconds value. There is no timezone information in this class. Thus just as Date this class simply holds the number of milliseconds since January 1, 1970, 00:00:00 GMT + nanos.

In PreparedStatement.setTimestamp(int parameterIndex, Timestamp x, Calendar cal) Calendar is used by the driver to change the default timezone. But Timestamp still holds milliseconds in GMT.

API is unclear about how exactly JDBC driver is supposed to use Calendar. Providers seem to feel free about how to interpret it, e.g. last time I worked with MySQL 5.5 Calendar the driver simply ignored Calendar in both PreparedStatement.setTimestamp and ResultSet.getTimestamp.

edited Aug 26 '15 at 04:51

answered Dec 28 '12 at 14:42

Evgeniy Dorofeev

133,369
30
199
275

Interested comment about MySQL 5.5 – Alex Dec 19 '13 at 09:03
6

I believe this to be wrong as of java 8+. Inside the Timestamp (because it extends from java.util.Date) there is a calendar attribute, which has a zoneinfo attribute that in fact contains the timezone. You can still get the UTC time with .getTime() because the Timestamp also stores it, but it HAS timezone information. – Jorge.V May 31 '19 at 11:37
1

you mean it holds the number of milliseconds since January 1, 1970, 00:00:00 UTC rather than GMT. GMT has daylight savings time. UNIX epoch is milliseconds since January 1, 1970, 00:00 UTC. – Kirby Mar 03 '20 at 22:22

score 12 · Answer 3 · answered Jul 24 '20 at 17:49

The answer is that java.sql.Timestamp is a mess and should be avoided. Use java.time.LocalDateTime instead.

So why is it a mess? From the java.sql.Timestamp JavaDoc, a java.sql.Timestamp is a "thin wrapper around java.util.Date that allows the JDBC API to identify this as an SQL TIMESTAMP value". From the java.util.Date JavaDoc, "the Date class is intended to reflect coordinated universal time (UTC)". From the ISO SQL spec a TIMESTAMP WITHOUT TIME ZONE "is a data type that is datetime without time zone". TIMESTAMP is a short name for TIMESTAMP WITHOUT TIME ZONE. So a java.sql.Timestamp "reflects" UTC while SQL TIMESTAMP is "without time zone".

Because java.sql.Timestamp reflects UTC its methods apply conversions. This causes no end of confusion. From the SQL perspective it makes no sense to convert a SQL TIMESTAMP value to some other time zone as a TIMESTAMP has no time zone to convert from. What does it mean to convert 42 to Fahrenheit? It means nothing because 42 does not have temperature units. It's just a bare number. Similarly you can't convert a TIMESTAMP of 2020-07-22T10:38:00 to Americas/Los Angeles because 2020-07-22T10:30:00 is not in any time zone. It's not in UTC or GMT or anything else. It's a bare date time.

java.time.LocalDateTime is also a bare date time. It does not have a time zone, exactly like SQL TIMESTAMP. None of its methods apply any kind of time zone conversion which makes its behavior much easier to predict and understand. So don't use java.sql.Timestamp. Use java.time.LocalDateTime.

LocalDateTime ldt = rs.getObject(col, LocalDateTime.class);
ps.setObject(param, ldt, JDBCType.TIMESTAMP);

score 9 · Answer 4 · answered Jul 24 '20 at 14:53

You can use the below method to store the timestamp in database specific to your desired zone/zone Id.

ZonedDateTime zdt = ZonedDateTime.now(ZoneId.of("Asia/Calcutta")) ;
Timestamp timestamp = Timestamp.valueOf(zdt.toLocalDateTime());

A common mistake people do is use LocaleDateTime to get the timestamp of that instant which discards any information specif to your zone even if you try to convert it later. It does not understand the Zone.

Please note Timestamp is of the class java.sql.Timestamp.

score 6 · Answer 5 · answered May 02 '16 at 13:06

For Mysql, we have a limitation. In the driver Mysql doc, we have :

The following are some known issues and limitations for MySQL Connector/J: When Connector/J retrieves timestamps for a daylight saving time (DST) switch day using the getTimeStamp() method on the result set, some of the returned values might be wrong. The errors can be avoided by using the following connection options when connecting to a database:

useTimezone=true
useLegacyDatetimeCode=false
serverTimezone=UTC

So, when we do not use this parameters and we call setTimestamp or getTimestamp with calendar or without calendar, we have the timestamp in the jvm timezone.

Example :

The jvm timezone is GMT+2. In the database, we have a timestamp : 1461100256 = 19/04/16 21:10:56,000000000 GMT

Properties props = new Properties();
props.setProperty("user", "root");
props.setProperty("password", "");
props.setProperty("useTimezone", "true");
props.setProperty("useLegacyDatetimeCode", "false");
props.setProperty("serverTimezone", "UTC");
Connection con = DriverManager.getConnection(conString, props);
......
Calendar nowGMT = Calendar.getInstance(TimeZone.getTimeZone("GMT"));
Calendar nowGMTPlus4 = Calendar.getInstance(TimeZone.getTimeZone("GMT+4"));
......
rs.getTimestamp("timestampColumn");//Oracle driver convert date to jvm timezone and Mysql convert date to GMT (specified in the parameter)
rs.getTimestamp("timestampColumn", nowGMT);//convert date to GMT 
rs.getTimestamp("timestampColumn", nowGMTPlus4);//convert date to GMT+4 timezone

The first method returns : 1461100256000 = 19/04/2016 - 21:10:56 GMT

The second method returns : 1461100256000 = 19/04/2016 - 21:10:56 GMT

The third method returns : 1461085856000 = 19/04/2016 - 17:10:56 GMT

Instead of Oracle, when we use the same calls, we have :

The first method returns : 1461093056000 = 19/04/2016 - 19:10:56 GMT

The second method returns : 1461100256000 = 19/04/2016 - 21:10:56 GMT

The third method returns : 1461085856000 = 19/04/2016 - 17:10:56 GMT

NB : It is not necessary to specify the parameters for Oracle.

I guess you could for oracle set the session timezone to UTC to achive the same conversion than MySQL does for the non-calendar case. "`ALTER SESSION SET TIME_ZONE = 'UTC'`". — eckes, Nov 07 '16 at 17:20

Woot4Moo · Answer 6 · 2012-12-28T14:08:45.127

5

It is specific from your driver. You need to supply a parameter in your Java program to tell it the time zone you want to use.

java -Duser.timezone="America/New_York" GetCurrentDateTimeZone

Further this:

to_char(new_time(sched_start_time, 'CURRENT_TIMEZONE', 'NEW_TIMEZONE'), 'MM/DD/YY HH:MI AM')

May also be of value in handling the conversion properly. Taken from here

edited Dec 28 '12 at 14:08

answered Dec 28 '12 at 14:01

Woot4Moo

23,987
16
94
151

do you meant me to follow http://stackoverflow.com/questions/2858182/preparedstatement-and-settimestamp-in-oracle-jdbc or You want to set my JVM application to run in GMT? – Kanagavelu Sugumar Dec 28 '12 at 14:08
@KanagaveluSugumar well that depends. If you want to modify the timestamp for EVERY query set it on startup, if you only want to modify SOME (meaning anything less than EVERY) then modify each query in the fashion as laid out by the post you referenced. – Woot4Moo Dec 28 '12 at 14:12

score -2 · Answer 7 · edited Mar 18 '21 at 12:19

-2

If your problem is to get a timestamp of the local zone, you can use this:

Timestamp.from(Instant.now()).toLocalDateTime()

edited Mar 18 '21 at 12:19

Frightera

4,773
2
13
28

answered Mar 18 '21 at 09:54

problem solver

1

Is java.sql.Timestamp timezone specific?

7 Answers7

Linked

Related