On 5 Jun 2015, at 9:14pm, Joe Mucchiello <jmucchiello at yahoo.com> wrote:
> All these subject lines about UDTs in SQLite and the one and only thing I > would use such a thing for is not listed: Date/Time values. I'd love for > there to be native date/time formats in SQLite. I'm surprised it never came > up. Although I'm also surprised the NoSQL-ite came up. 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) ? 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. Simon.
