title: TimeZone discrepancy in mysql and java
body: <p>I have a query in mysql which compares 2 dates like this</p>
  
  <pre><code>convert_tz(updatedDate,'+05:30','-05:00') &lt; ?
  </code></pre>
  
  <p>the convert function returns the value of column createddate in US Time. when I run this query in mysql query browser like</p>
  
  <pre><code>convert_tz(updatedDate,'+05:30','-05:00') &lt; '2013-04-14 09:30:00'
  </code></pre>
  
  <p>it gives me correct values for example</p>
  
  <pre><code>product    count
  -------    ------
      A        123
      B        7
  </code></pre>
  
  <p>Now, I am setting this in java using PreparedStatement like this</p>
  
  <pre><code>pst.setTimestamp(1, new java.sql.Timestamp(end.getTimeInMillis()));
  
                  rs=pst.executeQuery();
                  System.out.println("=====new Open Tickets Query executed=====");
                  System.out.println(pst);
  </code></pre>
  
  <p>the last line prints the whole query and the value set is</p>
  
  <pre><code>convert_tz(updatedDate,'+05:30','-05:00') &lt; '2013-04-14 09:30:00'
  </code></pre>
  
  <p>but it gives me different values like this</p>
  
  <pre><code>product    count
  -------    ------
      A        155
      B        19
  </code></pre>
  
  <p>So, I suspected that it is TimeZone problem I changed my code to</p>
  
  <pre><code>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);
  </code></pre>
  
  <p>but it still gives same wrong result.</p>
  
  <p><strong>More info: How I am setting Calendar end variable</strong></p>
  
  <p>I have a web application which gives me date string "2013-04-14 09:30:00"</p>
  
  <pre><code>            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);
  </code></pre>
  
  <p>Also, for experiment I tried with java.util.Date object it gives me correct result following is the code </p>
  
  <pre><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()));
  </code></pre>
  
  <p><strong>UPDATE :-</strong>
  If I use a deprecated method the answer is  correct</p>
  
  <pre><code> 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));
  </code></pre>
  
  <p><strong>UPDATE 2:-</strong>
  After the suggestion of first answer I did this</p>
  
  <p>1) executed <code>SELECT NOW()</code> in mysql and it returned <code>'2013-04-22 11:56:08'</code> </p>
  
  <p>2) executed </p>
  
  <pre><code>System.out.println(new Date(System.currentTimeMillis()));
  </code></pre>
  
  <p>output : <code>Mon Apr 22 11:56:25 IST 2013</code></p>
  
  <p>means both systems have same timezone</p>