On Wed, Dec 19, 2012 at 12:00 PM, <[email protected]> wrote: > Re: Suggested Improvement of Date Time Function (Larry Brasfield)
We currently store timestamps in this javams integer format which allows millisecond precision in a fixed 13 character/digit space. Our usage may not be too uncommon as we use this format for storing and graphing financial time series data. I believe FIX 40 messages are millisecond precise but some HFT data streams have microsecond resolution. This format feeds some of our java graphing libraries nicely. There are some advantages in performing integer based time manipulations and calculations over the string conversions. Currently we experience a small disconnect between unixepoch (second precision) and the built in string based date time functions that have ms or better precision. I believe it may be of general interest to at minimum have some integer time supported format that provides millisecond precision and matches java time format. Currently we just concatenate the ms onto the unixepoch time... Don't know if this is the best practice or where it may break other things but it works for our purposes. Our adaptation changes the upper and lower boundaries for sqlites unixepoch format as we restrict to 10 digits. The SQL code used to query and present data is kludgy and I'm certain we're not the only ones band-aiding these issues. Hence we opened this topic for discussion... If there is a better way to do this we are all ears. One of the performance issues we run into is grouping time series data. Ideally we would like the ability to group by timestamp/60000 for 1 minute resolution, timestamp/1000 for 1 second or change to any frequency without the query creating a btree for the grouping. When we store the timestamp/60000 value in an indexed column the groupings use the index and performs extremely well. Any help on creating dynamic grouped time series without creating btrees for grouping or storing the time calculation in an indexed column... if possible would be appreciated. _______________________________________________ sqlite-users mailing list [email protected] http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
