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

i storing datetime values in sqlite database (using delphi , disqlite library). nature of db such never need transferred between computers or systems, interoperability not constraint. focus instead on reading speed. datetime field indexed , searching on lot, reading in thousands of datetime values in sequence.

since sqlite not have explicit data type datetime values, there several options:

• use real data type , store delphi's tdatetime values directly: fastest, no conversion string on loading; impossible debug dates using db manager such sqlitespy, since dates not human-readable. cannot use sqlite date functions (?)

• use simple string format, e.g. yyyymmddhhnnss: conversion required relatively easy on cpu (no need scan separators), data human-readable. still cannot use sqlite date functions.

• do else. what's recommended thing do?

i have read http://www.sqlite.org/lang_datefunc.html there's no mention of data type use, and, not being formally schooled in programming, don't quite grok focus on julian dates. why additional conversion? reading in these values lot, additional conversions between strings , tdatetime adds significant cost.

you use 1 of sqlite supported string formats, eg. yyyy-mm-dd hh:mm:ss.sss.

it easy yyyymmddhhnnss - still wouldn't need scan separators, since numbers fixed length - , sqlite date function support.

if need sqlite date function support, go method.

if not, i'd recommend using real values. can still compare them each other (higher numbers later in time), , consider date , time separately (before , after decimal point respectively) without converting tdatetime.