JDBC Timestamp & Date GMT issues

Question

I have a JDBC Date column, which if a i use getDate is get the 'date' part only 02 Oct 2009 but if i use getTimestamp i get the full 'date' 02 Oct 2009 13:56:78:890. This is excatly what i want.

However the 'date' returned by getTimestamp 'ignores' the GMT values, suppose date; 02 Oct 2009 13:56:78:890, i end up getting 02 Oct 2009 15:56:78:890

My date was saved as a +2GMT date on the database but the application server is on GMT i.e 2hrs behind

How can still get my date as is, 02 Oct 2009 13:56:78:890

Edit

I get the date +2 on the client side that is on GMT +2

Answer 1

That's the difference between Timestamp and other temporal types in MySQL. Timestamp is saved as Unix time_t in UTC but other types store date/time literally without zone information.

When you call getTimestamp(), MySQL JDBC driver converts the time from GMT into default timezone if the type is timestamp. It performs no such conversion for other types.

You can either change the column type or do the conversion yourself. I recommend former approach.

Answer 2

You should be aware that java.util.Date (and also java.sql.Date and java.sql.Timestamp, which are subclasses of java.util.Date) don't know anything about timezones, or rather, they are always in UTC.

java.util.Date and its subclasses are nothing more than a container for a "number of milliseconds since 01-01-1970, 12:00 AM UTC" value.

To display a date in a specific timezone, convert it to a string by using a java.text.DateFormat object. Set the timezone on that object by calling the setTimeZone() method. For example:

Date date = ...;  // wherever you get this from

DateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

// Make the date format show the date in CET (central European time)
df.setTimeZone(TimeZone.getTimeZone("CET"));

String text = df.format(date);