<?xml version="1.0" ?><html>
<head>
<title></title>
</head>
<body>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
> CREATE TABLE Allan (DateTimeField DateTime); </span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
> INSERT INTO Allan (DateTimeField) VALUES ('2004-01-04 12:00:00');
</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
> SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) =
</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
> datetime('2004-01-04 12:00:00'); </span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
> SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) >
</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
> datetime('2004-01-03 12:00:00'); </span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
> SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) <
</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
> datetime('2004-01-03 12:00:00'); </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">I am using SQLite
from Delphi, and Delhi does milliseconds, so I have a time-
notation of YYYY-MM-DD HH:NN:SS:ZZZ standarized in my applications </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">It takes a trick in
Delphi to translate a DateTime to use also milliseconds, I use </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">EncodeTime from
string to timevar and FormatDateTime('hh:mm:ss:zzz',timevar)
from timevar to string. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">I insert the strings
in SQLite, and the conversion to time or from time take not
much time, in fact, I insert 10.000 records in about one and a half minute.
</span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">I benchmarked that
the conversion was 12 seconds, and the actually insertion was
77 seconds. ( a bit older PC, AMD 900Mhz, Windows-NT4.0) </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">The records also
contained small strings, floats, and are 15 fields wide. </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">For me that is very
satisfying. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
By dropping the transformation you permit the current version of sqlite to 
</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
use any relevant index to do these selections. </span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span
style="font-size:10pt">>  </span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
I don't know the ANSI standard to which you referred, but XSD defines the 
</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
datetime with a few extra tweaks: A T between the date and time, optional 
</span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
sub-second resolution, and an optional time-zone:  </span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span style="font-size:10pt">>
'2004-01-04T12:00:00+10:00'. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">So I would suggest
to use also milliseconds in a standard time-definition, if one
has to be choosen. </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">I would leave out
the "T", I have never seen this before, and it adds nothing extra
above a space. </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">A space does the
job, and is most often used, as far as I know. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">The timezone seems
OK at first glance, but I am not sure. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">You need the 0 in
for example +09:00  </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">And there is another
problem, I guess. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">-05:00 sorts before
-06:00. </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">So indexing for
sorting or comparing does not make any sence then. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial" color="#7f0000"><span
style="font-size:10pt">2004-01-04T</span></font><font face="Arial"><span
style="font-size:10pt">12:00:00:000-05:00
(7 o'clock Greenwich) </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">sorts as String
later then </span></font></div>
<div align="left"><font face="Arial" color="#7f0000"><span
style="font-size:10pt">2004-01-04T</span></font><font face="Arial"><span
style="font-size:10pt">11:00:00:000-02:00
(9 o'clock Greenwich) </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">thus in fact it is a
time passed earlier, and the sort is wrong. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">What do you do with
opcode IncrKey? </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">It needs a complex
indexing algoritm. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">IMHO timezones have
to be handeld by the application before adding the time to a
database, this keeps the database more quick. </span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">And milliseconds
should be used, set them to 000 when not used. </span></font></div>
<div align="left"><br/>
</div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Kind regards
</span></font></div>
<div align="left"><font face="Arial"><span style="font-size:10pt">Bert Verhees
</span></font></div>
<div align="left"><br/></div>
<div align="left"></div>
</body>
</html>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
