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

Reply via email to