Thanks Pavel for your replies. The reason looks to be the driver I am
using to connect sqlite. I have changed it to use the driver from xerial
and it is working fine now.
Sridhar
On 03-06-2011 12:00, Sridhar Polavarapu wrote:
> Here is the code of my TestStatusDate
>
> public static void main(String[] args) throws Exception
> {
> String jobQuery = "SELECT j.jobId, c.channelName ,
> datetime(j.jobCreateDate, 'localtime'), j.jobStatus, j.jobQuality,
> j.jobCompleteDate FROM Job j, Channel c where c.channelId =
> j.jobChannelId ORDER BY jobId DESC";
>
> long startTime = System.currentTimeMillis();
> Statement mStatement;
> ResultSet mResults;
> CachedRowSetImpl impl=null;
> Connection mconn = null;
> try
> {
> Class.forName("org.sqlite.JDBC");
> mconn = DriverManager.getConnection("jdbc:sqlite:" +
> "database.db");
> mStatement = mconn.createStatement();
> mResults = mStatement.executeQuery(jobQuery);
> impl = new CachedRowSetImpl();
> impl.populate(mResults);
> mStatement.close();
> mResults.close();
>
> }
> catch (SQLException e) {
> e.printStackTrace();
> }
> long endTime = System.currentTimeMillis();
>
> Locale l = Locale.getDefault();
> System.out.println("Today's Locale is " + l);
>
> System.out.println("timezone ==>" + (TimeZone.getDefault()));
>
> System.out.println("Time taken to execute query ==>" +(endTime
> - startTime));
>
> try {
> while(impl.next()){
> StringBuilder sb = new StringBuilder();
> sb.append(impl.getLong(1)+",");
> sb.append(impl.getString(2)+",");
> sb.append(impl.getString(3)+",");
> sb.append(impl.getString(4)+",");
> sb.append(impl.getString(5)+",");
> sb.append(impl.getString(6)+",");
>
> System.out.println(sb.toString());
>
> }
> } catch (SQLException e) {
> e.printStackTrace();
> }
>
> }
>
> On 02-06-2011 22:02, Pavel Ivanov wrote:
>> 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
>> <sridhar.polavar...@idc-rimage.com> 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
>>>> <sridhar.polavar...@idc-rimage.com> 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
>>>>>>> <sridhar.polavar...@idc-rimage.com> 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
>>>>>>>> sqlite-users@sqlite.org
>>>>>>>> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>>>>>>>>
>>>
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
