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

Reply via email to