javamysqltimezoneconvert-tz

TimeZone discrepancy in mysql and java

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

Solution

  • 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:

    Cheers!