On 2017/09/25 10:12 AM, David Wellman 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//....
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?
A short summary of considerations for that decision:
There are three standard date-time storage options (there are others
too, but these are the defacto go-to for most systems, all of which
SQLite handles well):
A - Storing an INTEGER Unix timestamp (Seconds or Milliseconds since a
base UTC date) or Julian day value,
B - Storing FLOAT/REAL where the Integer part describes the days since a
base date, typically 1/1/1900 or 1/1/1970, depending on who you roll
with and
C - Storing a string with a date or date and time, typically the
standard form is ISO8601 which looks like 'YYYY-MM-DDTHH:NN:SS.MSS
+ZZ:ZZ' with the T optionally being a space and the + could be + or - to
indicate offset (Zulu time) etc. Some parts are optional, there is a
good argument to not store Zulu time (offsets) but revert to UTC and let
the displaying app format the time to whatever time-frame the user
expects (much like Unix timestamps), so most actual stored dates end up
as simply 'YYYY-MM-DD HH:NN:SS' or even just 'YYYY-MM-DD' if not
interested in the time.
All of these have pro's and cons. Integer storage is usually most
efficient, but it takes some calculation to interpret, however SQLite is
very efficient at it, but if you want to do it in your own code, you
will need some standard functions to do so.
REAL/FLOAT is used by many systems (such as MS Excel) and it is a really
great storage method for doing time-intensive calculations and being
easy to understand by humans (well, a bit more-so than Unix stamps
anyway). Adding a day is as simple as adding 1 to the value (with the
Unix timestamp you have to add 86400), adding 12 hours (half a day) is
as easy as adding 0.5, etc. Subtracting 3 o'clock from 9 o'clock leaves
you with 0.25, which is a quarter of a day, so 6 hours exactly, and so on.
Strings take up some more space (though not that much actually) but has
the one significant advantage when trouble-shooting or debugging, that
the date is completely human readable as it is stored in the table, a
mere glance at the data will already tell you if the date is a factor in
whatever problem you are solving, whereas the other formats need a bit
of interpretation first (though very easy with a basic format statement
in the query). It has the disadvantage that, although SQLite provides
great and easy-to-use date-time adding/calculating functions, they do
take some more CPU cycles parsing the date string. (To be fair, I've
measured this at some point, and it was negligible, and even though it
was long ago, I doubt SQLite got worse at parsing dates.)
I always use ISO8601 strings storing UTC (non-offset) dates and times,
UNLESS data storage space is a big problem or date-time calculation is a
constant feature of the intended use of the table, then an Integer Unix
timestamp. If the date is simply stored and referenced in a normal way,
ISO date strings it is.
Note that these are my preferences when balancing data storage
considerations with ease of use and working/debugging efficiency, they
are by no means the "industry standard", if such a thing exists.
PS: If you do store ISO date strings, the suggested column TYPE in
SQLite terms is "NUMERIC" (not "TEXT"). At least that is what SQLite
itself relays DATE and DATETIME types to. I doubt it matters greatly,
but I use NUMERIC for any kind of date and it seems to work well, and if
I decide to change the date-time storage format later, it requires
merely an UPDATE query as opposed to a schema change.
Ref: Section 3.1.1 on this page:
http://www.sqlite.org/datatype3.html#affinity_name_examples
Cheers,
Ryan
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
