Hi,
We're designing a new feature which will involve a lot of date/time calculations which we intend to do within the SQLite engine. As far as we can tell it has the functions that we need. Basically we'll be loading data into SQLite and performing analysis and calculations using SQL. The question has come up as to how we should store date/time values in our tables? Basically how should we define our date/time columns? For us an obvious choice is to store dates/times as REAL. We're working in a Windows environment and so in the application code we're dealing with MS serial date values. Looking at the 'date and tine functions' page (http://www.sqlite.org/lang_datefunc.html ) it says "All five date and time functions take a time string as an argument". So my initial reaction is to store dates and times as TEXT. I think this means that when passing such date/time values into the functions there is one less conversion to do. But then looking at some of the examples on that page I came across the following: Compute the date and time given a unix timestamp 1092941466. SELECT datetime(1092941466, 'unixepoch'); In the sql syntax that I'm used to (which I thought was the same for SQLite) a series of digits like that shown above is treated as a numeric data value (and type), not text (i.e. a "time string"). If that was meant to be string data (i.e. text) then I'd have expected: SELECT datetime('1092941466', 'unixepoch'); So to clarify: Is our idea of storing date/time values as TEXT data a sensible one? We're essentially thinking of performance and there are almost certainly pros and cons to doing this. Pro: (I think) more efficient processing as the data is supplied to these functions as text which is what they're expecting/require - and therefore there is no additional conversion required. Con: Our full timestamps will be 19 bytes compared to a REAL which is only 8 bytes. So they will require more storage/disk space which ultimately means more I/O to read the same number of rows and columns. I accept that from a performance perspective there may not be much in it, but I'd be interested in people's thoughts. In anticipation, many thanks. Cheers, Dave Ward Analytics Ltd - information in motion Tel: +44 (0) 118 9740191 Fax: +44 (0) 118 9740192 www: <http://www.ward-analytics.com> http://www.ward-analytics.com Registered office address: The Oriel, Sydenham Road, Guildford, Surrey, United Kingdom, GU1 3SR Registered company number: 3917021 Registered in England and Wales. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
