[sqlite] UDTs

[Simon Slavin]

On 5 Jun 2015, at 9:14pm, Joe Mucchiello  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:

-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.

[sqlite] UDTs

[Joe Mucchiello]

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.

> That's six sets of knowledge to do one thing.? Granted, they do different 
> things in different ways but it's still rather a lot to fit into m noggin.? 
> And it's > annoying when you think you're debugging a JavaScript problem but 
> it turns out that a SQL command doesn't mean what you thought it did: 
> switching my> brain between JavaScripting and SQLing seems to require a lot 
> of effort.
Switching gears between on lang and another never bothered me in the slightest. 
They have different domains and thus different syntax spaces. Big deal.

[sqlite] UDTs

[Nico Williams]

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:
> 
> -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
--