First of all, very good, that de sqlite-list does not accept codes,
although it sometimes slips through.
Excuse for my previous mail.
But now, what I have to say.
> > CREATE TABLE Allan (DateTimeField DateTime);
> > INSERT INTO Allan (DateTimeField) VALUES ('2004-01-04 12:00:00');
> > SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) =
> > datetime('2004-01-04 12:00:00');
> > SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) >
> > datetime('2004-01-03 12:00:00');
> > SELECT DateTimeField FROM Allan WHERE datetime(DateTimeField) <
> > datetime('2004-01-03 12:00:00');
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
It takes a trick in Delphi to translate a DateTime to use also
milliseconds, I use
EncodeTime from string to timevar and FormatDateTime('hh:mm:ss:zzz',timevar)
from timevar to string.
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.
I benchmarked that the conversion was 12 seconds, and the actually
insertion was
77 seconds. ( a bit older PC, AMD 900Mhz, Windows-NT4.0)
The records also contained small strings, floats, and are 15 fields wide.
For me that is very satisfying.
> By dropping the transformation you permit the current version of sqlite to
> use any relevant index to do these selections.
>
> I don't know the ANSI standard to which you referred, but XSD defines the
> datetime with a few extra tweaks: A T between the date and time, optional
> sub-second resolution, and an optional time-zone:
> '2004-01-04T12:00:00+10:00'.
So I would suggest to use also milliseconds in a standard time-definition,
if one
has to be choosen.
I would leave out the "T", I have never seen this before, and it adds
nothing extra
above a space.
A space does the job, and is most often used, as far as I know.
The timezone seems OK at first glance, but I am not sure.
You need the 0 in for example +09:00
And there is another problem, I guess.
-05:00 sorts before -06:00.
So indexing for sorting or comparing does not make any sence then.
2004-01-04T12:00:00:000-05:00 (7 o'clock Greenwich)
sorts as String later then
2004-01-04T11:00:00:000-02:00 (9 o'clock Greenwich)
thus in fact it is a time passed earlier, and the sort is wrong.
What do you do with opcode IncrKey?
It needs a complex indexing algoritm.
IMHO timezones have to be handeld by the application before adding the
time to a
database, this keeps the database more quick.
And milliseconds should be used, set them to 000 when not used.
Kind regards
Bert Verhees
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
