UmI'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 wrote:
>
> > On Mar 8, 2017, at 12:52 PM, R Smith 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
