On Monday, 25 September, 2017 06:20, R Smith <rsm...@rsweb.co.za> wrote:
>On 2017/09/25 10:12 AM, David Wellman wrote: >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. To add to this, you can use any standard supported format, timestrings (ISO8601), julian day numbers (float), or Unix timestamps. The advantage of using either the Julian or Unix timestamp is that they are already UTC based (or ought to be) and just about everything can handle them. Using the timestring format can cause issues, however, because unless you "know" that the string is UTC, someone "just looking" or "editing" the database by hand may try to store a localtime value. In this case, obfuscation is better (I believe). Of course, an issue that you will run into, no matter what format you choose, is that Microsoft Products cannot convert between UTC and Localtime except for the current set of timezone rules (and assumes that the current rules are propeleptic), and you will need an external library and tables to do this conversion correctly. This is a limitation of the core runtime libraries and cannot be fixed if you let Windows Platform API's do the conversions. For this reason it is generally better to store a format such as the julian day or the unix timestamp so you do not have to deal with the vagaries of processing/converting "localtime" on Microsoft platforms. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
