On Fri, Jun 05, 2015 at 09:36:09PM +0100, Simon Slavin wrote: > One advantage I can think of of having a DateTime type -- enforcement > of storing the correct thing in the correct column -- won't work in > SQLite anyway, because rather than enforce column types is uses only > affinities. > > How would your code profit from having a DateTime type in SQLite, as > opposed to you just standardising on strings of the format described > in ISO8601: > > YYYY-MM-DDThh:mm:ssTZD > > ? Would you require another format too -- TimeInterval -- so that you > could subtract one DateTime from another ? Would you want automatic > reformatting of dates to and from your preferred date format (which > would require a list of locales to be added to SQLite) ?
Ideally numeric types (i.e., ones for whose values there are arithmetic operators) could have optional (but strongly typed and algebraic) dimensions and units, and an absolute vs. scalar (relative) form. This would help prevent many accidents (though unit conversions in a limited precision environment is a source of errors, so care is still required). Time should behave like a numeric type with "time" dimension and some unit such as seconds, say, or microseconds. The internal representation of time wouldn't have to be numeric. Type conversions -casts- to/from external representations (seconds since Unix epoch, seconds since Windows epoch, ISO8601 time strings, ...) should be available; some might be the identity function applied to the internal representation. (Also, obviously, there should be no time unit like "month".) A variant of the same type, coercing the internal representation to whatever is best for the user, would also be nice. > Strings of the above format can be searched and sorted. As long as > the programmer for a particular database is consistent about whether > they use TimeZones or not, the NOCASE collation works fine for > date/times. It's a solution that works fine without needing any extra > code in SQLite. I agree that it works well enough. Datetime is not really a great source of motivation for UDTs. If anything datetime is a poor motivation for UDTs because it is so special (having so many possible representations). If anything, UDTs are probably not as interesting as adding something like type-tagged blobs and maybe bitstrings. Nico --
