Optimal way to store datetime values in SQLite database (Delphi)

I will be storing datetime values in an SQLite database (using Delphi and the DISqlite library). The nature of the db is such that it will never need to be transferred between computers or systems, so interoperability is not a constraint. My focus instead 开发者_StackOverflow中文版is on reading speed. The datetime field will be indexed and I will be searching on it a lot, as well as reading in thousands of datetime values in sequence.

Since SQLite does not have an explicit data type for datetime values, there are several options:

• use REAL data type and store Delphi's TDateTime values directly: fastest, no conversion from string on loading; impossible to debug dates using a db manager such as SQLiteSpy, since dates will not be human-readable. Cannot use SQLite date functions (?)

• use a simple string format, e.g. YYYYMMDDHHNNSS: conversion is required but relatively easy on the CPU (no need to scan for separators), data is human-readable. Still cannot use SQLite date functions.

• do something else. What's the recommended thing to do?

I have read http://www.sqlite.org/lang_datefunc.html but there's no mention of what data type to use, and, not being formally schooled in programming, I don't quite grok the focus on Julian dates. Why the additional conversion? I will be reading in these values a lot, so any additional conversions between strings and TDateTime adds a significant cost.

You could use one of the SQLite supported string formats, eg. YYYY-MM-DD HH:MM:SS.SSS.

It would be just as easy as YYYYMMDDHHNNSS - you still wouldn't need to scan for separators, since all the numbers are fixed length - and you would get SQLite date function support.

If you need SQLite date function support, I would go with that method.

If not, I'd recommend using REAL values. You can still compare them to each other (higher numbers are later in time), and consider date and time separately (before and after the decimal point respectively) without converting to TDateTime.

One compromise would be to stick with REAL values, but store them as julian dates by using Delphi's DateTimeToJulianDate. That way they remain fast for reading, there's little performance lost in the converation, and they're still in a format that makes sense outside of Delphi.

For this I usually use an Integer data type and store the Unix timestamp value alike (eq # seconds since 1-1-2000 for example). Calculating this t/from a TDateTime is equal to multiplying/diving with/by 86400 and adding a constant for the 'since'.

If you need more precision You could use the DateTime as a FILETIME (eg int64) which has 100 ns increments. There are conversion routines in SysUtils for that and your timestamp is stored in UTC.

If your concern is only human readable format at database level, you can store two separate fields, for example:

DELPHI_DATE REAL (DOUBLE, if possible, but I don't know SQLite), Indexed. All your programmatic queries and comparisons should use this field.

HUMAN_READABLE_DATE Varchar(23) with format 'YYYY-MM-DD HH:MM:SS.SSS'. Maybe indexed (if really necessary). Majority of human input queries should include this field and you can use (as said by others) SQLite date functions.

It has some drawbacks:

• Space consumption at database and network traffic grows,
• insert operations will take a bit more because necessary conversion,
• no automatic synch between values if updated outside your program

If it is suitable for your particular needs, is up to you.

I don't know if this answer is applicable to the DISqlite library but...
Below is some code that illustrates what works for me using both Delphi 2010 and Tim Anderson's SQLite3 wrapper.

SQL to create field:

  sSQL :=  'CREATE TABLE [someTable] (' +
            '  [somefield1] VARCHAR(12),' +
            '  [somefield2] VARCHAR(12),' +
            '  [myDateTime] DATETIME );';

SQL to populate field:

 sSQL := 'INSERT INTO someTable(somefield1, somefield2, myDateTime)' + 
         '  VALUES ( "baloney1", "baloney2","' + FloatToStr(Now) + '");';

Example of retrieving data from field:

var
sDBFilePathString: string;
sl3tbl: TSqliteTable;
fsldb : TSQLiteDatabase;
FromdbDTField : TDateTime;

begin
   ...
   ... 
    fsldb := TSQLiteDatabase.Create(sDBFilePathString); 
    sl3tbl := fsldb.GetTable('SELECT * FROM someTable');
    FromdbDateTime := StrToFloat(sl3tbl.FieldAsString(sl3tbl.FieldIndex['myDateTime']));
    Showmessage('DT: ' + DateTimeToStr(FromdbDTField));
end;

Result:

**DT: 10/10/2013 1:09:53 AM**

Like I mentioned on the first line - I don't know if this will work with the DISqlite library but if it does its a pretty clean way of handling things. I leave it to you to make things prettier or more elegant.