David, unless you're wanting to use SQLite's built-in datetime operators, then just encode yours somehow and put them in another field type, and decode them on retrieval into your own datetime types. Depending what you encode them as, pick the appropriate built-in type. -- Darren Duncan
On 2016-02-08 5:00 PM, david at andl.org wrote: > Having read and understood the documentation on Sqlite data types, I'm really > just looking for a single recommendation on which choices to make. > > I need to store generic data in 5 types: bool, binary, number/decimal, > text/nvarchar, time/date/datetime. Decimal has more than 15 digits of > precision. Text is Unicode. Time is years 0000-9999 with fractional seconds. > > For each type I need to choose: > > 1. Type name (string) to use in CREATE TABLE. > 2. Affinity. > 3. Datatype that will be returned by C API calls > 4. Which C API call to use to get and put data values. > > My current choices are: > Bool: TINYINT, INTEGER, value_int > Binary: BLOB, BLOB, value_blob > Number: NUMERIC, NUMERIC, value_??? > Text: TEXT, TEXT, Encoding utf-8, value_text > Time: DATETIME, NUMERIC, value_???. > > The first two are easy enough. No problems. > > Number: should I choose text functions like sqlite3_value_text() and do all > my own conversions, or is there benefit in using a different function > according to the storage class/data type? Or is sqlite3_value_numeric_type() > the way to go? > > Text: I assume this just works, and all text values are UTF-8 by default? > > Time: the only supported DATETIME format seems to be ISO8601, which has no > explicit C API support? That looks like a lot of conversion overhead for > something that is easily stored in a 64-bit integer. What would > sqlite3_value_numeric_type() do? > > [Has there been any consideration of extending the range of types to include > decimal and a binary datetime?] > > Sorry if it's a bit scrappy, but I just need to make some choices and then go > away and write the code. > > Regards > David M Bennett FACS > > Andl - A New Database Language - andl.org
