On 2017/10/11 7:15 PM, Don V Nielsen wrote:
So strftime always returns TEXT. Correct?
Yes. The "str" in "strftime" means "string" which is text output. You
can read the name "strftime" as "string-formatted-time value".
It was the application of +300 to
that result that changed the type to INTEGER. And had "+300 seconds" been
applied as a modifier in the strftime function, then the addition would
have occurred before producing the result, with the result being type TEXT.
Yes. When you concatenate/add a string and integer together some SQL
engines will try to give a sensible result, so that '5' + 3 will yield 8
because 3 is INT and it reckons that '5' probably meant 5 since it is
added to another INT and the 5 doesn't have an explicit type. But, if
you force one of the terms to be string, such as CAST( 5 AS TEXT) or
strftime(%s,5) or use the value as a parameter to a function that
expects a specific type, then the engine might assume stuff, or try to
make sense of it, but in general when you start these shenanigans you
are on thin ice over "UNDEFINED" territory, which is what bit the OP
since the result in one version of SQlite differed from another version
(which the devs might adjust, but it's not a bug since mixing types is
not strictly supported).
You should never mix types when you expect a certain output. use CAST to
force the type you need, especially before arithmetic. Don't leave
correct interpretation up to the engine, even if it works mostly.
sqlite-users mailing list