Internally SQLite stores and process numbers as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C.
I have not examined the code in any depth but would assume that if you store the data in the same format it would save on any processing overhead for calculations but would just require a conversion for display. Paul www.sandersonforensics.com skype: r3scue193 twitter: @sandersonforens Tel +44 (0)1326 572786 http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit -Forensic Toolkit for SQLite email from a work address for a fully functional demo licence On 25 September 2017 at 09:12, David Wellman <dwell...@ward-analytics.com> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
