TimeZone discrepancy in mysql and java

Question

I have a query in mysql which compares 2 dates like this

convert_tz(updatedDate,'+05:30','-05:00') < ?

the convert function returns the value of column createddate in US Time. when I run this query in mysql query browser like

convert_tz(updatedDate,'+05:30','-05:00') < '2013-04-14 09:30:00'

it gives me correct values for example

product    count
-------    ------
    A        123
    B        7

Now, I am setting this in java using PreparedStatement like this

pst.setTimestamp(1, new java.sql.Timestamp(end.getTimeInMillis()));

                rs=pst.executeQuery();
                System.out.println("=====new Open Tickets Query executed=====");
                System.out.println(pst);

the last line prints the whole query and the value set is

convert_tz(updatedDate,'+05:30','-05:00') < '2013-04-14 09:30:00'

but it gives me different values like this

product    count
-------    ------
    A        155
    B        19

So, I suspected that it is TimeZone problem I changed my code to

end.setTimeZone(TimeZone.getTimeZone("America/New York"));
pst.setTimestamp(1, new java.sql.Timestamp(end.getTimeInMillis()));

                rs=pst.executeQuery();
                System.out.println("=====new Open Tickets Query executed=====");
                System.out.println(pst);

but it still gives same wrong result.

More info: How I am setting Calendar end variable

I have a web application which gives me date string "2013-04-14 09:30:00"

            DateFormat df1=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                Calendar end=Calendar.getInstance();
                end.setTime(df1.parse(endString));
                end.set(Calendar.HOUR, 9);
                end.set(Calendar.MINUTE, 30);
                end.set(Calendar.SECOND, 0);

Also, for experiment I tried with java.util.Date object it gives me correct result following is the code

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
end.setTime(sdf.parse("2012-10-01 00:00:00"));
pst.setTimestamp(1, new java.sql.Timestamp(end.getTime()));

UPDATE :- If I use a deprecated method the answer is correct

 pst.setTimestamp(1, new java.sql.Timestamp(octDate.get(Calendar.YEAR)-1900,octDate.get(Calendar.MONTH),octDate.get(Calendar.DATE),octDate.get(Calendar.HOUR),octDate.get(Calendar.MINUTE),octDate.get(Calendar.SECOND),0));
pst.setTimestamp(2, new java.sql.Timestamp(end.get(Calendar.YEAR)-1900,end.get(Calendar.MONTH),end.get(Calendar.DATE),end.get(Calendar.HOUR),end.get(Calendar.MINUTE),end.get(Calendar.SECOND),0));

UPDATE 2:- After the suggestion of first answer I did this

1) executed SELECT NOW() in mysql and it returned '2013-04-22 11:56:08'

2) executed

System.out.println(new Date(System.currentTimeMillis()));

output : Mon Apr 22 11:56:25 IST 2013

means both systems have same timezone

Solicitude

MySQL's time zone information has no bearing on your problem, since MySQL is not handling the job of parsing the date. See my answer. – Uncompromising

Answer 1

Background: A surprisingly common--and big--misconception shared by even brilliant programmers is the notion that stored time stamps (in your database, Date, Calendar, Timestamp, et al) somehow have time zone information. They do not. A time stamp (up until Java 8, anyway) is stored as the number of milliseconds since midnight on 1 Jan 1970 UTC. End of sentence. The only thing setting the time zone does is provide enough information to the computer to convert that time stamp to a human readable format, and vice versa.

Answer: When you suspected that this was a time zone problem, you were right. But the code you used to try to verify this also has a problem:

end.setTimeZone(TimeZone.getTimeZone("America/New York"));
pst.setTimestamp(1, new java.sql.Timestamp(end.getTimeInMillis()));

That setTimeZone statement has no effect on the time stored in end, because the time has already been set. It would only have had an effect if you stored the time afterwards, and then only if you used one of Calendar's methods which converted the time from a human readable format (and not setTimeInMillis).

When you use getTimeInMillis to pass the time stamp to your prepared statement, you're retrieving the time stamp directly. Since you're not converting it to a human format, once again the time zone information is ignored.

When you try

SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
end.setTime(sdf.parse("2012-10-01 00:00:00"));
pst.setTimestamp(1, new java.sql.Timestamp(end.getTime()));

and

pst.setTimestamp(1, new java.sql.Timestamp(octDate.get(Calendar.YEAR)-1900,octDate.get(Calendar.MONTH),octDate.get(Calendar.DATE),octDate.get(Calendar.HOUR),octDate.get(Calendar.MINUTE),octDate.get(Calendar.SECOND),0));
pst.setTimestamp(2, new java.sql.Timestamp(end.get(Calendar.YEAR)-1900,end.get(Calendar.MONTH),end.get(Calendar.DATE),end.get(Calendar.HOUR),end.get(Calendar.MINUTE),end.get(Calendar.SECOND),0));

things appear to work because you are now using methods which convert to/from a human readable format, and therefore the specified time zone information is used. However, this is only covering up the real problem. The real issue is that the time was improperly converted when you parsed it from endString. That is, the time zone that endString was expressed in does not match the time zone set in df1 at the time the date was parsed.

SHORT ANSWER: before this line:

end.setTime(df1.parse(endString));

You need to:

• Figure out what time zone the time in endString was expressed in.
• Set df1 and not end to that same time zone. Since df1 is the thing that is converting the date from human format, it's that time zone information that's used.

Cheers!

Uncompromising

@Matt Johnson also makes a very good point. In all places where you are converting a time from text, make sure you're using a location based time zone and not an offset ("America/New York" and not "EST"). – Uncompromising

Answer 2

Which value is returned from your TimeZone.getDefault().getID() and from MySQL default time zone?

BTW you can try using an utility like this one to convert datetime across timezones:

public Calendar convertDateToServerTimeZone(Date dateTime, String timeZone) {
    Calendar userDefinedTime = Calendar.getInstance();
    userDefinedTime.setTime(dateTime);
    if(!TimeZone.getDefault().getID().equalsIgnoreCase(timeZone)) {
    System.out.println        ("original defined time: " + userDefinedTime.getTime().toString() + " on tz:" + timeZone);
    Calendar quartzStartDate = new GregorianCalendar(TimeZone.getTimeZone(timeZone));
    quartzStartDate.set(Calendar.YEAR, userDefinedTime.get(Calendar.YEAR));
    quartzStartDate.set(Calendar.MONTH, userDefinedTime.get(Calendar.MONTH));
    quartzStartDate.set(Calendar.DAY_OF_MONTH, userDefinedTime.get(Calendar.DAY_OF_MONTH));
    quartzStartDate.set(Calendar.HOUR_OF_DAY, userDefinedTime.get(Calendar.HOUR_OF_DAY));
    quartzStartDate.set(Calendar.MINUTE, userDefinedTime.get(Calendar.MINUTE));
    quartzStartDate.set(Calendar.SECOND, userDefinedTime.get(Calendar.SECOND));
    quartzStartDate.set(Calendar.MILLISECOND, userDefinedTime.get(Calendar.MILLISECOND));
    System.out.println("adapted time for " + TimeZone.getDefault().getID() + ": " + quartzStartDate.getTime().toString());
    return quartzStartDate;
    } else {
    return userDefinedTime;
    }
}

I have worked with this function here: Java Quartz-Scheduler across TimeZone

Hope his helps.

Glennieglennis

I don't think there is a problem of timezone here. because when I use a deprecated method it works fine. when I put dates directly in query the result is correct. – Solicitude

I don't think MySQL's time zone is the problem. MySQL's time zone information is only used when it handles the job of converting the time from/to text. Since Bhavik is passing the time as a parameter in a prepared statement, it has already been converted. If Bhavik had placed the time as text directly in the query, then MySQL's zone information would have been used to parse it. See my answer. – Uncompromising

Answer 3

Without seeing the values that are matching on the second query and not on the first, it's hard to be absolutely certain. But one thing to keep in mind is that a time zone is not the same as an offset. Please read "TimeZone != Offset" section of the TimeZone tag wiki.

For example, you say you are converting to the America/New_York time zone. This zone is sometimes in UTC-05:00 (for Eastern Standard Time), and sometimes in UTC-04:00 (for Eastern Daylight Time). It is entirely possible that some of your data is being picked up because of the -4 offset in effect during daylight savings time.

When you hardcode to the -5 offset, you are not taking any time zone rules into consideration. Which would explain the discrepancy.

Sustentation

Answer 4

Timezone information is not applied to dates/times unless you either set useTimeZone=true in your connection URL. You can use the getTimestamp() method also, which take calendars as an argument.

Extensible

Even then, it has no effect on how the time is stored. It only has an effect on how the time is displayed/parsed. See my answer. – Uncompromising

Answer 5

Give this a try.

Set timestamp parameter as you are setting now

pst.setTimestamp(1, new java.sql.Timestamp(end.getTimeInMillis()));

.

During database call, JDBC driver may already be converting input date/time values to GMT. So the date value that database is dealing with won't have to take timezones of input date into considerations which may differ from one client to other.

Instead of setting from_tz as '+05:30'

convert_tz(updatedDate,'+05:30','-05:00')

Set from_tz as '00:00'

convert_tz(updatedDate,'00:00','-05:00') 

Rurik