Re: [sqlite] Subtract times hh:mm:ss
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the data that
gets different answers and your code?
You can' even get fractional seconds from those statements as the time format
only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik itandet...@mvps.org wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
Igor Tandetnik
___
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subtract times hh:mm:ss
There are no different answers and I think all the information is in the
first post.
RBS
On Oct 7, 2012 1:21 PM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the data
that gets different answers and your code?
You can' even get fractional seconds from those statements as the time
format only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik itandet...@mvps.org
wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
Igor Tandetnik
___
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
___
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
Re: [sqlite] Subtract times hh:mm:ss
You expect the readers on this list to go find your old post and then look at
what you're NOT doing now? You asked how to compute time, we showed you, and
now you are apparently doing it incorrectly.
You need to provide enough info in your current post for people to duplicate
your current problem and want to help you.
You showed you are getting 3 different answers...presumably from the same
record...but you don't show us the fields you are computing it from, nor the
code which does it.
Come to think of of itthere was no question in your last post either.
So help us help you.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Sunday, October 07, 2012 8:18 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
There are no different answers and I think all the information is in the
first post.
RBS
On Oct 7, 2012 1:21 PM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the data
that gets different answers and your code?
You can' even get fractional seconds from those statements as the time
format only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik itandet...@mvps.org
wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
Igor Tandetnik
___
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
___
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subtract times hh:mm:ss
The word times in my previous post confused/misled you. These are execution
times, not values produced by SQL.
RBS
On Sunday, October 7, 2012, Black, Michael (IS) wrote:
You expect the readers on this list to go find your old post and then look
at what you're NOT doing now? You asked how to compute time, we showed
you, and now you are apparently doing it incorrectly.
You need to provide enough info in your current post for people to
duplicate your current problem and want to help you.
You showed you are getting 3 different answers...presumably from the same
record...but you don't show us the fields you are computing it from, nor
the code which does it.
Come to think of of itthere was no question in your last post either.
So help us help you.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;] on behalf of Bart
Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Sunday, October 07, 2012 8:18 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
There are no different answers and I think all the information is in the
first post.
RBS
On Oct 7, 2012 1:21 PM, Black, Michael (IS)
michael.bla...@ngc.comjavascript:;
wrote:
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the data
that gets different answers and your code?
You can' even get fractional seconds from those statements as the time
format only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;]
on behalf of Bart Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik
itandet...@mvps.orgjavascript:;
wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
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
Re: [sqlite] Seemingly random Access Violation errors
Currently using version 83. Is this correct? Started on 82. Will give the device a factory reset to be sure but manually deleted previous copy of dll on the device before compiling the version with your last set of changes (managed to find where to download the whole of the source code this time.) Would it help at all if I sent you the .kdmp files produced when the crash occurs? You might be able to make more sense of them than me. On 06/10/2012 07:11, Joe Mistachkin wrote: Joe Mistachkin wrote: I do not have a real device to test the code on. Also, the project you sent is targeted to a different emulator version than the ones I currently have available. I'll try to manually retarget the project tonight and try to reproduce the issue in more detail. I am not able to reproduce the issue by running the sample code you sent in the emulator against the latest trunk code. Are you sure that you are using a System.Data.SQLite DLL compiled from the latest code in the source repository and not accidentally using some other DLL that may be present on the target system? One of the reasons I'm asking is because I noticed the System.Data.SQLite DLLs you sent were version 82, which they would not be if they were compiled from the code in trunk. -- Joe Mistachkin ___ 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
Re: [sqlite] Subtract times hh:mm:ss
That makes a LOT more sense...misread times as times :-)
Sorry for any rant on my part.
Are all your times via your VB app?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on
behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Sunday, October 07, 2012 9:15 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
The word times in my previous post confused/misled you. These are execution
times, not values produced by SQL.
RBS
On Sunday, October 7, 2012, Black, Michael (IS) wrote:
You expect the readers on this list to go find your old post and then look
at what you're NOT doing now? You asked how to compute time, we showed
you, and now you are apparently doing it incorrectly.
You need to provide enough info in your current post for people to
duplicate your current problem and want to help you.
You showed you are getting 3 different answers...presumably from the same
record...but you don't show us the fields you are computing it from, nor
the code which does it.
Come to think of of itthere was no question in your last post either.
So help us help you.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;] on behalf of Bart
Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Sunday, October 07, 2012 8:18 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
There are no different answers and I think all the information is in the
first post.
RBS
On Oct 7, 2012 1:21 PM, Black, Michael (IS)
michael.bla...@ngc.comjavascript:;
wrote:
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the data
that gets different answers and your code?
You can' even get fractional seconds from those statements as the time
format only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;]
on behalf of Bart Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik
itandet...@mvps.orgjavascript:;
wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
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
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subtract times hh:mm:ss
No trouble, it was somewhat confusing. Yes, all done through the VB wrapper.
RBS
On Oct 7, 2012 7:38 PM, Black, Michael (IS) michael.bla...@ngc.com
wrote:
That makes a LOT more sense...misread times as times :-)
Sorry for any rant on my part.
Are all your times via your VB app?
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org]
on behalf of Bart Smissaert [bart.smissa...@gmail.com]
Sent: Sunday, October 07, 2012 9:15 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
The word times in my previous post confused/misled you. These are execution
times, not values produced by SQL.
RBS
On Sunday, October 7, 2012, Black, Michael (IS) wrote:
You expect the readers on this list to go find your old post and then
look
at what you're NOT doing now? You asked how to compute time, we showed
you, and now you are apparently doing it incorrectly.
You need to provide enough info in your current post for people to
duplicate your current problem and want to help you.
You showed you are getting 3 different answers...presumably from the same
record...but you don't show us the fields you are computing it from, nor
the code which does it.
Come to think of of itthere was no question in your last post either.
So help us help you.
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;] on behalf of Bart
Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Sunday, October 07, 2012 8:18 AM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
There are no different answers and I think all the information is in the
first post.
RBS
On Oct 7, 2012 1:21 PM, Black, Michael (IS) michael.bla...@ngc.com
javascript:;
wrote:
You haven't provided enough info for anybody to tell what's going on.
What data are you substracting? Can you provide an sql dump of the
data
that gets different answers and your code?
You can' even get fractional seconds from those statements as the time
format only supports hr/min/sec
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Advanced GEOINT Solutions Operating Unit
Northrop Grumman Information Systems
From: sqlite-users-boun...@sqlite.org javascript:; [
sqlite-users-boun...@sqlite.org javascript:;]
on behalf of Bart Smissaert [bart.smissa...@gmail.com javascript:;]
Sent: Saturday, October 06, 2012 12:38 PM
To: General Discussion of SQLite Database
Subject: EXT :Re: [sqlite] Subtract times hh:mm:ss
Times I get (65000 records, subtracting 2 fields defined as text in
the same table)
gives me following times:
method with julianday 0.4 secs
method with unixepoch 0.6 secs
using ctime etc. via VB wrapper 1.2 secs
RBS
On Tue, Oct 2, 2012 at 7:39 PM, Igor Tandetnik itandet...@mvps.org
javascript:;
wrote:
On 10/2/2012 1:00 PM, Bart Smissaert wrote:
Is there a way to subtract times in the text format hh:mm:ss
and return the difference in the same format?
select time(julianday('03:22:11') - julianday('01:22:33') - .5);
select time(strftime('%s', '03:22:11') - strftime('%s', '01:22:33'),
'unixepoch');
Both of these return '01:59:38'.
--
Igor Tandetnik
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org javascript:;
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
___
sqlite-users mailing list
Re: [sqlite] Subtract times hh:mm:ss
Am 06.10.2012 19:38, schrieb Bart Smissaert: Times I get (65000 records, subtracting 2 fields defined as text in the same table) gives me following times: method with julianday 0.4 secs method with unixepoch 0.6 secs using ctime etc. via VB wrapper 1.2 secs What? A VB-implemented User-Defined-Function slower than a built-in C-function? Now, there's a challenge... ;-) Since I'm in the last stages for a new wrapper- version (RichClient5) - I've tried to speed these two functions up already in the new Binary (FWIW). Wasn't all that difficult, because the former VB.Runtime- function (Format$,... yes, I was lazy), which internally was playing a major role in these conversions, is not the fastest and leaves enough room for improvement. So, after optimization the UDFs CTime/CDbl are now about twice as fast as the time/julianday-functions. If you want to try it out, here's a download of the current snapshot of the new wrapper: www.datenhaus.de/Downloads/vbRC5BaseDlls.zip After registering you should be able to run the following testcode (TestTable contains 86400 increasing seconds). Tried to avoid the selection of too many records in the test-query (only a single one is returned), so that the test can run more or less completely inside SQLites VDBE. The printed results, after scanning over 86400 records are: Count: 1 Timing VB-UDF: 0,049s Count: 1 Timing SQLite: 0,090s --- used table-layout and VB-testcode --- '*Into a Form, then click the Form Option Explicit Private MemDB As New cMemDB, Rs As cRecordset, T! Private Sub Form_Load() With MemDB.NewFieldDefs .Add ID Integer Primary Key .Add T1 Text .Add T2 Text MemDB.CreateTable T End With With MemDB.CreateCommand(Insert Into T Values(@ID,@T1,@T2)) MemDB.BeginTrans Dim i As Long For i = 0 To 86400 - 1 '-one day (increasing seconds in T2) .SetNull !ID .SetText !T1, 00:00:00 .SetText !T2, Format$(i / 86400, hh:mm:ss) .Execute Next i MemDB.CommitTrans End With End Sub Private Sub Form_Click() Const Where1 = CTime(CDbl(T2) - CDbl(T1)) = '23:59:59' Const Where2 = time(julianday(T2) - julianday(T1) - .5) = '23:59:59' T = Timer Set Rs = MemDB.GetTable(T, Where1) T = Timer - T Print Count:; Rs.RecordCount, Timing VB-UDF: ; Format(T, 0.000s) T = Timer Set Rs = MemDB.GetTable(T, Where2) T = Timer - T Print Count:; Rs.RecordCount, Timing SQLite: ; Format(T, 0.000s) Print End Sub Olaf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subtract times hh:mm:ss
Hi Olaf, Will give that a go. I take it these files are not ready yet to put in a commercial app? RBS On Sun, Oct 7, 2012 at 10:23 PM, Olaf Schmidt s...@online.de wrote: Am 06.10.2012 19:38, schrieb Bart Smissaert: Times I get (65000 records, subtracting 2 fields defined as text in the same table) gives me following times: method with julianday 0.4 secs method with unixepoch 0.6 secs using ctime etc. via VB wrapper 1.2 secs What? A VB-implemented User-Defined-Function slower than a built-in C-function? Now, there's a challenge... ;-) Since I'm in the last stages for a new wrapper- version (RichClient5) - I've tried to speed these two functions up already in the new Binary (FWIW). Wasn't all that difficult, because the former VB.Runtime- function (Format$,... yes, I was lazy), which internally was playing a major role in these conversions, is not the fastest and leaves enough room for improvement. So, after optimization the UDFs CTime/CDbl are now about twice as fast as the time/julianday-functions. If you want to try it out, here's a download of the current snapshot of the new wrapper: www.datenhaus.de/Downloads/vbRC5BaseDlls.zip After registering you should be able to run the following testcode (TestTable contains 86400 increasing seconds). Tried to avoid the selection of too many records in the test-query (only a single one is returned), so that the test can run more or less completely inside SQLites VDBE. The printed results, after scanning over 86400 records are: Count: 1 Timing VB-UDF: 0,049s Count: 1 Timing SQLite: 0,090s --- used table-layout and VB-testcode --- '*Into a Form, then click the Form Option Explicit Private MemDB As New cMemDB, Rs As cRecordset, T! Private Sub Form_Load() With MemDB.NewFieldDefs .Add ID Integer Primary Key .Add T1 Text .Add T2 Text MemDB.CreateTable T End With With MemDB.CreateCommand(Insert Into T Values(@ID,@T1,@T2)) MemDB.BeginTrans Dim i As Long For i = 0 To 86400 - 1 '-one day (increasing seconds in T2) .SetNull !ID .SetText !T1, 00:00:00 .SetText !T2, Format$(i / 86400, hh:mm:ss) .Execute Next i MemDB.CommitTrans End With End Sub Private Sub Form_Click() Const Where1 = CTime(CDbl(T2) - CDbl(T1)) = '23:59:59' Const Where2 = time(julianday(T2) - julianday(T1) - .5) = '23:59:59' T = Timer Set Rs = MemDB.GetTable(T, Where1) T = Timer - T Print Count:; Rs.RecordCount, Timing VB-UDF: ; Format(T, 0.000s) T = Timer Set Rs = MemDB.GetTable(T, Where2) T = Timer - T Print Count:; Rs.RecordCount, Timing SQLite: ; Format(T, 0.000s) Print End Sub Olaf ___ 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
Re: [sqlite] Subtract times hh:mm:ss
Am 07.10.2012 23:43, schrieb Bart Smissaert: I take it these files are not ready yet to put in a commercial app? Not yet, but in 2-3 weeks the COM-interfaces of the new added Classes (as for eaxmple the new convenience-class cMemDB) should be stabilized and contain their final method-signatures (binary compatibility is not yet switched on for vbRichClient5.dll). So if you (or others who are using the RichClient-COM-libs) have some suggestions for more convenience-stuff or otherwise useful additions, now is the time to speak-up (not here in the group, just drop me an E-Mail with your wish-list and I see what I can include into RC5). Olaf ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Subtract times hh:mm:ss
Have tested and indeed a lot faster now. Again the same test, so 65000 records subtracting the same 2 fields: method with julianday 0.4 secs method with unixepoch 0.6 secs using ctime etc. via VB wrapper 0.27 secs RBS On Sun, Oct 7, 2012 at 10:23 PM, Olaf Schmidt s...@online.de wrote: Am 06.10.2012 19:38, schrieb Bart Smissaert: Times I get (65000 records, subtracting 2 fields defined as text in the same table) gives me following times: method with julianday 0.4 secs method with unixepoch 0.6 secs using ctime etc. via VB wrapper 1.2 secs What? A VB-implemented User-Defined-Function slower than a built-in C-function? Now, there's a challenge... ;-) Since I'm in the last stages for a new wrapper- version (RichClient5) - I've tried to speed these two functions up already in the new Binary (FWIW). Wasn't all that difficult, because the former VB.Runtime- function (Format$,... yes, I was lazy), which internally was playing a major role in these conversions, is not the fastest and leaves enough room for improvement. So, after optimization the UDFs CTime/CDbl are now about twice as fast as the time/julianday-functions. If you want to try it out, here's a download of the current snapshot of the new wrapper: www.datenhaus.de/Downloads/vbRC5BaseDlls.zip After registering you should be able to run the following testcode (TestTable contains 86400 increasing seconds). Tried to avoid the selection of too many records in the test-query (only a single one is returned), so that the test can run more or less completely inside SQLites VDBE. The printed results, after scanning over 86400 records are: Count: 1 Timing VB-UDF: 0,049s Count: 1 Timing SQLite: 0,090s --- used table-layout and VB-testcode --- '*Into a Form, then click the Form Option Explicit Private MemDB As New cMemDB, Rs As cRecordset, T! Private Sub Form_Load() With MemDB.NewFieldDefs .Add ID Integer Primary Key .Add T1 Text .Add T2 Text MemDB.CreateTable T End With With MemDB.CreateCommand(Insert Into T Values(@ID,@T1,@T2)) MemDB.BeginTrans Dim i As Long For i = 0 To 86400 - 1 '-one day (increasing seconds in T2) .SetNull !ID .SetText !T1, 00:00:00 .SetText !T2, Format$(i / 86400, hh:mm:ss) .Execute Next i MemDB.CommitTrans End With End Sub Private Sub Form_Click() Const Where1 = CTime(CDbl(T2) - CDbl(T1)) = '23:59:59' Const Where2 = time(julianday(T2) - julianday(T1) - .5) = '23:59:59' T = Timer Set Rs = MemDB.GetTable(T, Where1) T = Timer - T Print Count:; Rs.RecordCount, Timing VB-UDF: ; Format(T, 0.000s) T = Timer Set Rs = MemDB.GetTable(T, Where2) T = Timer - T Print Count:; Rs.RecordCount, Timing SQLite: ; Format(T, 0.000s) Print End Sub Olaf ___ 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
[sqlite] how to write sql cmd for this situation:
I have a 2 table: 1. tb1: aa bb cc mdate 1 d1 2.5 2012-08-07 2 d2 3.3 2012-08-07 3 d1 6.4 2012-08-09 2. tb2: aa bb cc 1. d1 5.3 2. d2 7.6 mysql sql cmd is: select tb2.bb,tb2.cc,(max(tb1.mdate)-min(tb1.mdate)) as mmd,(mmd. max(tb1.mdate).cc-mmd. min(tb1.mdate).cc) as mmc from tb1,tb2 where tb2.bb in (select bb from tb1 where bb group by bb having count(bb)) ; I wanna the result is: bb ccmmd mmc d1 5.3 23.9 the result 3.9 mean is 6.4-2.5 , mdate 2012-08-09 - 2012-08-07 =2, and 6.4 - 2.5=3.9 how to get the result by sqm cmd? thanks. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] how to write sql cmd for this situation:
YAN HONG YE yanhong...@mpsa.com wrote: mysql sql cmd is: select tb2.bb,tb2.cc,(max(tb1.mdate)-min(tb1.mdate)) as mmd,(mmd. max(tb1.mdate).cc-mmd. min(tb1.mdate).cc) as mmc from tb1,tb2 where tb2.bb in (select bb from tb1 where bb group by bb having count(bb)) ; With all due respect, this query doesn't make any sense. Could you explain in your own words exactly what data do you want to calculate? -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Seemingly random Access Violation errors
Matthew Dumbleton wrote: Currently using version 83. Is this correct? Started on 82. I assume you are building from the source code in the repository then? Are you using the included (built from source) SQLite.Interop.dll or a standard Win32 sqlite3.dll with the managed assembly? What command line and/or options are you using when building the binaries? Are any custom extensions to SQLite being used? Will give the device a factory reset to be sure but manually deleted previous copy of dll on the device before compiling the version with your last set of changes (managed to find where to download the whole of the source code this time.) Resetting the device may fix the issue if it resulted from a corrupt .NET Compact Framework installation or similar issue; however, do you think that is likely? Would it help at all if I sent you the .kdmp files produced when the crash occurs? You might be able to make more sense of them than me. If you send it along with the built binaries and their associated PDB files I may be able to determine something, yes. -- Joe Mistachkin ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
