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
