TimeZone discrepancy in mysql and java -

i have query in mysql compares 2 dates this

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

the convert function returns value of column createddate in time. when run query in mysql query browser like

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

it gives me correct values example

product    count -------    ------            123     b        7 

now, setting in java using preparedstatement 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 whole query , value set is

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

but gives me different values this

product    count -------    ------            155     b        19 

so, suspected timezone problem changed 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 still gives same wrong result.

more info: how setting calendar end variable

i have web application 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, experiment tried java.util.date object gives me correct result following 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 use deprecated method answer 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 suggestion of first answer did this

1) executed select now() in mysql , 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

background: surprisingly common--and big--misconception shared brilliant programmers notion stored time stamps (in database, date, calendar, timestamp, et al) somehow have time zone information. they not. time stamp (up until java 8, anyway) stored number of milliseconds since midnight on 1 jan 1970 utc. end of sentence. thing setting time zone provide enough information computer convert time stamp human readable format, , vice versa.

answer: when suspected time zone problem, you right. code used try verify has problem:

end.settimezone(timezone.gettimezone("america/new york")); pst.settimestamp(1, new java.sql.timestamp(end.gettimeinmillis())); 

that settimezone statement has no effect on time stored in end, because time has been set. have had effect if stored time afterwards, , if used 1 of calendar's methods converted time human readable format (and not settimeinmillis).

when use gettimeinmillis pass time stamp prepared statement, you're retrieving time stamp directly. since you're not converting human format, once again time zone information ignored.

when 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 work because using methods convert to/from human readable format, , therefore specified time zone information used. however, covering real problem. real issue time improperly converted when parsed endstring. is, time zone endstring expressed in not match time zone set in df1 @ time date parsed.

short answer: before line:

end.settime(df1.parse(endstring)); 

you need to:

• figure out time zone time in endstring expressed in.
• set df1 , not end same time zone. since df1 thing converting date human format, it's time zone information that's used.

cheers!