I have a datetime type field some_field in a MySQL database. I can query the data using recordSet.getTimestamp("somefield"), which returns a java.sql.Timestamp object. However, the returned values stores a UTC time. Now if know the time zone is "America/Chicago", how can I convert the time. I have to take care of DST.
1 Answer
I think you may need to ignore the sub-millisecond precision of Timestamp - which is probably reasonable, as if you're converting to a particular time zone, that suggests it's meant to be a "human" date. Anyway:
TimeZone zone = TimeZone.getTimeZone("America/Chicago");
Calendar calendar = Calendar.getInstance(zone); // Also locale?
calendar.setTime(timestamp);
Now you can ask the calendar for whatever values you want - date, hour of day etc.
Alternatively, you might want to consider using Joda Time, which is a much better date/time API:
DateTimeZone zone = DateTimeZone.forID("America/Chicago");
DateTime dateTime = new DateTime(timestamp.getTime(), zone);
8 Comments
sean hawk
Problem... Now in the database I have a time '2012-07-31 16:00:00' and the timezone is
Timezone.getTimeZone("US/Eastern"). But the calendar.get(Calendar.HOUR_OF_DAY) is 17. I think that's because my server's tz is "US/Central"? What shall I do?
Jon Skeet
@seanhawk: You need to determine for sure the exact value returned to the Java code. If you print out
timestamp.getTime() what does it show? (I won't be able to help until tomorrow, but I'll have a look then.)sean hawk
When the time is "2012-07-31 20:22:03" and timezone is "US/Pacific",
timestamp.getTime() is 1343784123000, which is "Wed, 01 Aug 2012 01:22:03 GMT" (see www.onlineconversion.com/unix_time.htm). This means resultSet.getTimestamp() treats the result as if it is in the app server's timezone, which is "US/Central" in my case. BTW, resultSEt.getTimestamp(key, cal) gets exactly same Timestamp.Jon Skeet
@seanhawk: It sounds like you need to get what's stored in the database straight. Is it meant to store the data as a US/Central time? If you change the time zone, does that change the meaning of the data that's already stored? That would be pretty nasty.
sean hawk
I have two columns in the DB. One is of type
datetime (in the case of MySQL, it is basically a string and has no timezone info). The other is a varchar that stores the timezone. The data are from all over the country, so there are different timezones. I don't want to change the structure of the DB as it isn't my design. I don't know about the other DBMS, but I feel the date/time types in MySQL is pretty nasty. |
getString()instead ofgetTimestamp(). Java's datetime API stinks.