Um....I'm one of those sad old hacks who doesn't store dates at all. Just Years, Months, and Days. You can do just about anything with them stored that way, and in sqlite it seems to matter little whether they are string or numeric columns. Dates are a rubbery concept not well suited to db storage IMHO. ;-)
On 11 March 2017 at 05:35, Jens Alfke <j...@mooseyard.com> wrote: > > > On Mar 8, 2017, at 12:52 PM, R Smith <rsm...@rsweb.co.za> wrote: > > > >> Interestingly I rarely see dates stored in ISO8601 format/text > > > > Because every programmer is a self-proclaimed optimization genius! > > In this case it often makes sense to optimize in advance. In multiple > situations over the years I’ve seen date-string parsing be a major > bottleneck, in operations like database indexing and file reading. It’s > surprisingly expensive; some of that is due to handling the weirdnesses of > human date systems, but a lot seems to be because the typical functions > have to handle arbitrary formats and decipher the format string as well as > the input. (I’ve found you can do a lot better with a function that’s > hardcoded to parse a specific date format.) > > > If speed/space isn't critical, I always advise ISO8601 dates, typically > stored (in SQLite anyway) in a NUMERIC typed column. > > I basically agree, it’s just that the speed seems to be critical more > often than one would think :) > > At least some date formats, including ISO-8601 with times in UTC, have the > feature that you can compare dates as strings without having to parse them. > That makes sorting by date a lot faster. > > —Jens > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Regards, Michael.j.Falconer. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
