DELPHI: Sqlite Date & Time Format, How? Please Help

Question

I'm new to SQLite, and I was wondering what is the best format in SQLite for storing Date and Time?

I tried to save Date (similarly TIME too) using something like this (I set SQLite Date/Time field as CHAR)

SQLStr:='INSERT INTO MYTABLE ([DATE]) VALUES ('+#39+FormatDateTime('YYYY-MM-DD',MyDate)+#39+')';

The ExecSql worked (I think) since I can see the added date in the table.

But when I try to open the field using StrToDate function, it always says, for example, 2010-11-23 is not a valid date.

I'm not sure, but I think StrToDate is not appropriate for this operation. If so, what is?

Help?

Thanks! :)

I use D5,ZeosLib 6.6.6, and Sqlite3.dll

Answer 1

To convert the string in the database to date/time, you can use StrToDate(const S: string; const AFormatSettings: TFormatSettings).

The AFormatSettings parameter can be used to configure the date and time separators.

---

You can not declare date types in SQLite 3:

See: http://www.sqlite.org/datatype3.html

The following types can be used:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
• REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
• INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC

In Delphi, as one way to use date/time database field types, you could use a calculated field of TDateTimeField type and perform the conversion in the OnCalcFields event.

Update: to convert from Unix Time to TDateTime, you can use the UnixToDateTime function in unit DateUtils.

Answer 2

I store Delphi dates as Julian dates, and handle the conversion between them using Delphi's DateTimeToJulianDate and JulianDateToDateTime. I do a lot of queries involving dates, and storing dates as numeric (REAL) values rather than TEXT values enables more efficient querying, ex:

SELECT * from table WHERE date(julianday(datecolumn, "LOCALTIME"))=date(julianday("now", "LOCALTIME"))

Answer 3

Since I once asked the same exact question, perhaps the answers I got will help: Optimal way to store datetime values in SQLite database (Delphi)

FWIW, I'm storing TDateTime directly as REAL values in SQLite. They're not human-readable if you peek directly at the database, but it's faster and easier than using strings, since no conversion is performed. Delphi has enough date manipulation methods of its own to make the ones built into SQLite redundant.

Of course my way is not recommended if your database will ever be accessed by apps not written in Delphi, since they will not understand Delphi's TDateTime.