And your TestStatusDate code is? I'd bet now that the problem is in the way you get that time from jdbc. It can convert the time back to utc for you, although it looks a little strange that it converts one date and doesn't convert other one...
Pavel On Thu, Jun 2, 2011 at 11:52 AM, Sridhar Polavarapu <[email protected]> wrote: > I am on Windows 7 64 bit; I have tested the program as you mentioned, the > good point here was the issue still appears please find logs . Let me know > if you infer anything from below. > > C:\development\SignalCoreClient\test\com\Rimage\Starfish\AutomatedTests>java > TestStatusDate > Today's Locale is en_IN > timezone > ==>sun.util.calendar.ZoneInfo[id="Asia/Calcutta",offset=19800000,dstSavings=0,useDaylight=false,transitions=6,lastRule=null] > Time taken to execute query ==>3623 > 762,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:53,COMPLETE,md,2011-06-02 13:26:14, > 761,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:52,COMPLETE,md,2011-06-02 13:26:07, > 760,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:52,COMPLETE,md,2011-06-02 13:25:59, > 759,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:51,COMPLETE,md,2011-06-02 13:25:41, > 758,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:50,COMPLETE,md,2011-06-02 13:25:36, > 757,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:50,COMPLETE,md,2011-06-02 13:25:30, > 756,automatedTest_13067437059065180049472173,2011-06-02 > 13:24:49,COMPLETE,md,2011-06-02 13:25:23, > 755,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:09,COMPLETE,md,2011-06-02 13:17:30, > 754,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:08,COMPLETE,md,2011-06-02 13:17:25, > 753,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:07,COMPLETE,md,2011-06-02 13:17:12, > 752,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:07,COMPLETE,md,2011-06-02 13:16:51, > 751,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:06,COMPLETE,md,2011-06-02 13:16:48, > 750,automatedTest_130675855965820033590511079,2011-06-02 > 13:16:05,COMPLETE,md,2011-06-02 13:19:17, > 749,automatedTest_1307018964144181250169479584,2011-06-02 > 12:49:30,COMPLETE,hi,2011-06-02 12:57:58, > 748,NaveenTest,2011-06-02 12:49:29,COMPLETE,hi,2011-06-02 12:56:59, > 747,automatedTest_1307018192020180478065994419,2011-06-02 > 12:36:39,COMPLETE,hi,2011-06-02 12:56:30, > 746,NaveenTest,2011-06-02 12:36:37,COMPLETE,hi,2011-06-02 12:55:33, > 745,automatedTest_130675855965720033590323324,2011-06-02 > 11:52:21,COMPLETE,md,2011-06-02 11:52:54, > 744,automatedTest_1306482594629320685646725968,null,COMPLETE,md,2011-06-02 > 10:18:47, > 743,automatedTest_130675534731016821286104758,2011-06-02 > 15:15:25,INIT,md,null, > 742,automatedTest_130675534731016821286104758,2011-06-02 > 15:08:15,INIT,md,null, > 741,automatedTest_130675534731016821286104758,2011-06-02 > 09:34:06,COMPLETE,md,2011-06-02 09:34:46, > 740,automatedTest_130675855965820033590602240,null,COMPLETE,md,2011-06-02 > 09:33:59, > 739,automatedTest_130675855965820033590602240,2011-06-02 > 14:56:11,INIT,md,null, > 738,automatedTest_130675855965820033590602240,2011-06-02 > 09:25:39,INIT,md,null, > 737,NaveenTest,2011-06-02 13:54:34,INIT,md,null, > 736,NaveenTest,2011-06-02 08:24:02,COMPLETE,md,2011-06-02 08:25:51, > 735,SridharTest,2011-06-02 13:23:24,INIT,md,null, > 734,SridharTest,2011-06-02 07:51:44,COMPLETE,md,2011-06-02 07:56:07, > 733,automatedTest_1306481519407319610451279968,2011-06-02 > 13:11:25,INIT,md,null, > 732,automatedTest_1306481519407319610451279968,2011-06-02 > 07:40:45,COMPLETE,md,2011-06-02 07:45:47, > 731,automatedTest_130675534731016821286048311,2011-06-02 > 07:25:25,COMPLETE,md,2011-06-02 07:28:14, > 730,automatedTest_130675534731016821286048311,2011-06-02 > 12:54:59,INIT,md,null, > 729,automatedTest_13067429149993217266307076,2011-06-02 > 07:12:25,COMPLETE,md,2011-06-02 07:16:24, > 728,SridharTest,2011-06-02 07:01:59,COMPLETE,md,2011-06-02 07:02:25, > 727,automatedTest_1306496245310334336328084528,2011-06-02 > 06:54:48,COMPLETE,md,2011-06-02 06:55:29, > 726,automatedTest_1306481519407319610444456560,2011-06-01 > 07:00:33,COMPLETE,md,2011-06-01 07:01:04, > 725,automatedTest_130651279595727994816196128,2011-05-31 > 13:56:47,COMPLETE,md,2011-05-31 13:57:44, > 724,automatedTest_130651279595727994816196128,2011-05-31 > 13:56:47,COMPLETE,md,2011-05-31 13:57:24, > 723,automatedTest_130651279595727994816196128,2011-05-31 > 13:56:46,COMPLETE,md,2011-05-31 13:57:20, > 722,automatedTest_130651279595727994816196128,2011-06-02 > 12:22:02,COMPLETE,md,2011-05-31 13:57:15, > > C:\development\SignalCoreClient\test\com\Rimage\Starfish\AutomatedTests>cd > \Users\sridhar\Desktop > > C:\Users\sridhar\Desktop>sqlite3.exe C:\Signal\Publisher\publisher.db > SQLite version 3.7.6.3 > Enter ".help" for instructions > Enter SQL statements terminated with a ";" > sqlite> SELECT j.jobId, c.channelName , datetime(j.jobCreateDate, > 'localtime'), j.jobStatus, j.jobQuality, j.jobCompleteDate FROM Job j, > Channel c where c > elId = j.jobChannelId ORDER BY jobId DESC; > 762|automatedTest_13067437059065180049472173|2011-06-02 > 18:54:53|COMPLETE|md|2011-06-02 13:26:14 > 761|automatedTest_13067437059065180049472173|2011-06-02 > 18:54:52|COMPLETE|md|2011-06-02 13:26:07 > 760|automatedTest_13067437059065180049472173|2011-06-02 > 18:54:52|COMPLETE|md|2011-06-02 13:25:59 > 759|automatedTest_13067437059065180049472173|2011-06-02 > 18:54:51|COMPLETE|md|2011-06-02 13:25:41 > 758|automatedTest_13067437059065180049472173|2011-06-02 > 18:54:50|COMPLETE|md|2011-06-02 13:25:36 > 757|automatedTest_13067437059065180049472173|2011-06-02 > 18:54:50|COMPLETE|md|2011-06-02 13:25:30 > 756|automatedTest_13067437059065180049472173|2011-06-02 > 18:54:49|COMPLETE|md|2011-06-02 13:25:23 > 755|automatedTest_130675855965820033590511079|2011-06-02 > 18:46:09|COMPLETE|md|2011-06-02 13:17:30 > 754|automatedTest_130675855965820033590511079|2011-06-02 > 18:46:08|COMPLETE|md|2011-06-02 13:17:25 > 753|automatedTest_130675855965820033590511079|2011-06-02 > 18:46:07|COMPLETE|md|2011-06-02 13:17:12 > 752|automatedTest_130675855965820033590511079|2011-06-02 > 18:46:07|COMPLETE|md|2011-06-02 13:16:51 > 751|automatedTest_130675855965820033590511079|2011-06-02 > 18:46:06|COMPLETE|md|2011-06-02 13:16:48 > 750|automatedTest_130675855965820033590511079|2011-06-02 > 18:46:05|COMPLETE|md|2011-06-02 13:19:17 > 749|automatedTest_1307018964144181250169479584|2011-06-02 > 18:19:30|COMPLETE|hi|2011-06-02 12:57:58 > 748|NaveenTest|2011-06-02 18:19:29|COMPLETE|hi|2011-06-02 12:56:59 > 747|automatedTest_1307018192020180478065994419|2011-06-02 > 18:06:39|COMPLETE|hi|2011-06-02 12:56:30 > 746|NaveenTest|2011-06-02 18:06:37|COMPLETE|hi|2011-06-02 12:55:33 > 745|automatedTest_130675855965720033590323324|2011-06-02 > 17:22:21|COMPLETE|md|2011-06-02 11:52:54 > 744|automatedTest_1306482594629320685646725968||COMPLETE|md|2011-06-02 > 10:18:47 > 743|automatedTest_130675534731016821286104758|2011-06-02 20:45:25|INIT|md| > 742|automatedTest_130675534731016821286104758|2011-06-02 20:38:15|INIT|md| > 741|automatedTest_130675534731016821286104758|2011-06-02 > 15:04:06|COMPLETE|md|2011-06-02 09:34:46 > 740|automatedTest_130675855965820033590602240||COMPLETE|md|2011-06-02 > 09:33:59 > 739|automatedTest_130675855965820033590602240|2011-06-02 20:26:11|INIT|md| > 738|automatedTest_130675855965820033590602240|2011-06-02 14:55:39|INIT|md| > 737|NaveenTest|2011-06-02 19:24:34|INIT|md| > 736|NaveenTest|2011-06-02 13:54:02|COMPLETE|md|2011-06-02 08:25:51 > 735|SridharTest|2011-06-02 18:53:24|INIT|md| > 734|SridharTest|2011-06-02 13:21:44|COMPLETE|md|2011-06-02 07:56:07 > 733|automatedTest_1306481519407319610451279968|2011-06-02 18:41:25|INIT|md| > 732|automatedTest_1306481519407319610451279968|2011-06-02 > 13:10:45|COMPLETE|md|2011-06-02 07:45:47 > 731|automatedTest_130675534731016821286048311|2011-06-02 > 12:55:25|COMPLETE|md|2011-06-02 07:28:14 > 730|automatedTest_130675534731016821286048311|2011-06-02 18:24:59|INIT|md| > 729|automatedTest_13067429149993217266307076|2011-06-02 > 12:42:25|COMPLETE|md|2011-06-02 07:16:24 > 728|SridharTest|2011-06-02 12:31:59|COMPLETE|md|2011-06-02 07:02:25 > 727|automatedTest_1306496245310334336328084528|2011-06-02 > 12:24:48|COMPLETE|md|2011-06-02 06:55:29 > 726|automatedTest_1306481519407319610444456560|2011-06-01 > 12:30:33|COMPLETE|md|2011-06-01 07:01:04 > 725|automatedTest_130651279595727994816196128|2011-05-31 > 19:26:47|COMPLETE|md|2011-05-31 13:57:44 > 724|automatedTest_130651279595727994816196128|2011-05-31 > 19:26:47|COMPLETE|md|2011-05-31 13:57:24 > 723|automatedTest_130651279595727994816196128|2011-05-31 > 19:26:46|COMPLETE|md|2011-05-31 13:57:20 > 722|automatedTest_130651279595727994816196128|2011-06-02 > 17:52:02|COMPLETE|md|2011-05-31 13:57:15 > > On 02-06-2011 20:33, Pavel Ivanov wrote: >>> >>> I have verified my locale and default time zone in the same program.The >>> values are correct and it is not UTC timezone. Any help appreciated. >> >> How did you do that? What OS are you running it on? >> Can you extract the code accessing SQLite database from your program >> and create a mini-program that does nothing else except executes this >> query? Run this mini-program, see what it returns. Then from the same >> shell prompt run sqlite3 command line utility and execute this query >> there, see if results are different. If they are show us full text of >> java mini-program, we will try to find the problem. >> >> >> Pavel >> >> >> On Thu, Jun 2, 2011 at 10:57 AM, Sridhar Polavarapu >> <[email protected]> wrote: >>> >>> I have verified my locale and default time zone in the same program.The >>> values are correct and it is not UTC timezone. Any help appreciated. >>> >>> Sridhar >>> >>> On 02-06-2011 19:34, Sridhar Polavarapu wrote: >>>> >>>> The returned value is displayed to the user. We are not storing back the >>>> returned column. >>>> >>>> Sridhar >>>> >>>> On 02-06-2011 19:28, Pavel Ivanov wrote: >>>>>> >>>>>> but is returning the value stored in the database when executed >>>>>> as a part of a java program. Can anyone help me if I am missing >>>>>> something here ? >>>>> >>>>> Looks like your java program runs with a different locale than SQLite >>>>> Manager. I'm not sure why it thinks that you are in a UTC timezone. >>>>> Maybe there's something in starting scripts or in initialization code >>>>> of this program? >>>>> >>>>> >>>>> Pavel >>>>> >>>>> >>>>> On Thu, Jun 2, 2011 at 7:32 AM, Sridhar Polavarapu >>>>> <[email protected]> wrote: >>>>>> >>>>>> Hi >>>>>> >>>>>> I am preparing a string sql query as follows >>>>>> >>>>>> String jobQuery = "SELECT j.jobId, c.channelName , >>>>>> datetime(j.jobCreateDate,'localtime') as jobCreateDate, j.jobStatus, >>>>>> CASE WHEN j.jobQuality='md' THEN 'Medium' WHEN j.jobQuality='hi' THEN >>>>>> 'High' WHEN j.jobQuality='lo' THEN 'Low' ELSE 'Unspecified' END AS >>>>>> jobQuality, j.jobCompleteDate "; >>>>>> jobQuery += "FROM Job j "; >>>>>> jobQuery += "JOIN Channel c ON c.channelId = j.jobChannelId "; >>>>>> >>>>>> this query gives me jobCreateDate in my localtime when run in sqlite >>>>>> manager, but is returning the value stored in the database when >>>>>> executed >>>>>> as a part of a java program. Can anyone help me if I am missing >>>>>> something here ? >>>>>> >>>>>> Sridhar >>>>>> _______________________________________________ >>>>>> sqlite-users mailing list >>>>>> [email protected] >>>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >>>>>> >>> > > _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
