If selecting rows according to a date/timestamp is ever needed, numeric time stamps are clearly advantageous, e.g.
SELECT * FROM data WHERE t BETWEEN julianday(tbegstr) AND julianday(tendstr); is much more efficient than SELECT * FROM data WHERE julianday(tstr) BETWEEN julianday(tbegstr) AND julianday(tendstr); particularly if there is an index on t in the first SELECT; Sqlite's date and time functions support Julian Day, which is in some circumstances not the best: 1) With 64 bit floats the resolution is about 1 millisecond which is not sufficient for some real life technical data. 2) It cannot handle leap seconds, such as the one that will be inserted on June 30, 2015. An alternative is a "day segmented time code", e. g. CREATE TABLE timestamped_data ( day2000 INTEGER, --nr of days since Jan 1,2000, 0 UTC msec INTEGER, --nr of milliseconds in day usec INTEGER, --microseconds in msec ... ) -- to speed up searches in time: CREATE INDEX ON timestamped_data (day2000,msec,usec); day2000 can be stored in 16 bit for contemporary data; usec is optional, 16 bit would be enough, msec of course in 32 bit. I have an Sqlite extension cds2datestr(day2000,msec,usec) returning a human readable format (only yyyy-MM-ddThh:mm:ss.sss supported) which I would be happy to share. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
