mORMot Open Source

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

TDateTime range selects

There doesn't seem an easy way to do an SQL select on a date range with TDateTime fields as it's stored as a string. What is the best field type to use in a table where I want to do date range searches?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: TDateTime range selects

Use TTimeLog kind of field, which is an Int64, so is easy to compare and index.

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: TDateTime range selects

Thanks for the quick response. Would it make sense to have TDateTime stored as a floating point like in Delphi and have a separate type for ISO 8601 dates?

esmondb

Member

From: London

Registered: 2010-07-20

Posts: 299

Re: TDateTime range selects

Just read the docs: 3.1.4. Floating point and Currency fields, and think I see why it doesn't make sense

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,660

Website

Re: TDateTime range selects

ISO 8601 is pretty standard in database, and it is the default SQLite3 format.
You have dedicated functions in SQLite SQL: see http://www.sqlite.org/lang_datefunc.html
Problem is that SQLite does not allow to create an index from a function result (AFAIK) so the engine has to lookup the whole table to perform a date query.

Delphi TDateTime is the COM date/time format, but even .NET does not use it.
Our Int64=TTimeLog format is not standard either, but easy to work with, and very efficient for speed (it uses bit maps, and no multiplication/division).