Re: GNATCOLL SQLite3 vs Ada.Calendar.Time

["G.B." <bauhaus@futureapps.invalid>]

On 19.08.16 17:30, Stephen Leake wrote:
> On Thursday, August 18, 2016 at 1:13:40 PM UTC-5, Stephen Leake wrote:
>> On Thursday, August 18, 2016 at 12:45:11 PM UTC-5, Stephen Leake wrote:
>>> On Thursday, August 18, 2016 at 6:19:45 AM UTC-5, G.B. wrote:
>>>> On 17.08.16 23:05, Stephen Leake wrote:
>>>>> One solution would be to replace the "DATETIME" field type in the .sql file with "TEXT", always pass strings for times, and rely on lexicographic sorting for time comparison. But that seems extreme.
>>>>
>>>> It seems advisable to use SQLite's Date And Time Functions
>>>> when using SQLite for SQL. Thus,
>>>>
>>>>     " ... WHERE Modified >= datetime(?) ... "
>>>>
>>>
>>> Yes, that works.
>>
>> I take it back; it fixed most of the problems in the full program, but not all.
>>
>> So I'm switching to CHAR[19] for date/time fields.
>
> Which _also_ did not fix the problem. Which makes sense, given that SQL apparently uses TEXT for DATETIME.
>
> So I wrote code to substitute the time string for the ?, and pass a single string to SQLite3 with no bound parameters; that fixed the problem.
>
> There is apparently a problem with bound string parameters and >= in queries. Bound parameters with = INTEGER or LIKE %STRING work (so far, anyway).

TTBOMK, DATETIME, as opposed to TIMESTAMP, is not really present
in standard SQL (1999). DATE and TIME are. However, DATETIME and its
variations are offered by many DBMS products. There are hints at
varying ranges, though. Also, literals of DATETIME may differ.
Found these, for example, for two rather popular ones:

from: 1753-01-01 00:00:00.000	to: 31.12.9999 23:59:59.997
from: 1000-01-01 00:00:00	to: 9999-12-31 23:59:59

And for some version of Oracle, the docs say:
   "Dates between January 1, 4712 B.C. and December 31, 9999 A.D.,
    and times in hours, minutes and seconds."
DATE literals will depend on NLS settings.

For TIMESTAMP and TIME, timezones may enter the picture, as in
TIMESTAMP [WITH TIME ZONE]. Handling zones may depend on the DBMS
or even on SQL session settings, and is therefore not easy to
predict from just looking at SQL queries.

So, if portability or QoI are an issue, Randy Brukardt's suggestion
to use standard Ada conversions, to and from text, looks simple and
safe to me.

In the past, I had successfully used a combination of
DATE and TIME fields, not needing the fractions of TIMESTAMP.
The programs would be using UTC when storing points in time.
This worked reasonably well, since it didn't need complicated
comparisons of points in time. (In fact, when some results need
grouping by day, the split was a logical advantage.)