Hi Olaf,
Thanks, will give that a try today and tell you if it is faster.
RBS
On Wed, Oct 3, 2012 at 3:55 AM, Olaf Schmidt <s...@online.de> wrote:
> Am 02.10.2012 21:23, schrieb Bart Smissaert:
>
>> Nice one, thanks for that.
>
>
> Just in case you use the COM-Wrapper - and this operation takes
> place in a plain Recordset-Select (and isn't stored somewhere
> in the DB itself, e.g. in a Trigger), then you can reduce the
> amount of function-calls a bit, when you use something like that:
>
> Define the Table-Field with the wrapper-supported Time-FieldType,
> which ends up as 'hh:mm:ss' Text in the SQLite-DB-Field - but is
> correctly translated back into a Date-Type in the receiving cRecordset.
>
> To safe a few CPU-Cycles in the Query, you can directly place
> the Double-representation of a VB(A)-Date in the Query-String
> (done in the Example per ? Placeholder in a cSelectCommand).
>
> The Diff-expression in the Select then looks this way:
> CTime(? - CDbl(HMS))
>
> CDbl, to convert the TextContent of the HMS-Field into the
> Double-representation of a VB-Date - and CTime to
> convert the difference back into a 'hh:mm:ss' String.
>
> Not sure, if that is faster than SQLites built-in Date/Time-Functions,
> but worth a try...
>
>
> ' Into a Form (clicking the Form gives the Delta to its "LoadTime")
> ' Output then i.e.: HMS 04:21:27 True DTS 00:00:01 True
> Option Explicit
>
> Private Cnn As New cConnection, GetDeltaCmd As cSelectCommand
>
> Private Sub Form_Load()
> Cnn.CreateNewDB '<- InMemory
>
> 'the wrappers Time-FieldType ensures 'hh:mm:ss' TextFormat in the DB
> Cnn.Execute "Create Table T(HMS Time)"
>
> With Cnn.CreateCommand("Insert Into T Values(?)")
> .SetTime 1, Now()
> .Execute
> End With
>
> Const GetDeltaSQL = "Select HMS, CTime(? - CDbl(HMS)) As DTS From T"
> Set GetDeltaCmd = Cnn.CreateSelectCommand(GetDeltaSQL)
> End Sub
>
> Private Sub Form_Click()
> GetDeltaCmd.SetDouble 1, Now() 'we place the Param directly as Double
>
> With GetDeltaCmd.Execute 'returns a cRecordset
> Debug.Print !HMS.Name, !HMS.Value, VarType(!HMS.Value) = vbDate,
> Debug.Print !DTS.Name, !DTS.Value, VarType(!DTS.Value) = vbString
> End With
> 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
