From mboxrd@z Thu Jan 1 00:00:00 1970 X-Spam-Checker-Version: SpamAssassin 3.4.4 (2020-01-24) on polar.synack.me X-Spam-Level: X-Spam-Status: No, score=-0.3 required=5.0 tests=BAYES_00, REPLYTO_WITHOUT_TO_CC autolearn=no autolearn_force=no version=3.4.4 Path: eternal-september.org!reader01.eternal-september.org!reader02.eternal-september.org!news.eternal-september.org!news.eternal-september.org!.POSTED!not-for-mail From: "G.B." Newsgroups: comp.lang.ada Subject: Re: GNATCOLL SQLite3 vs Ada.Calendar.Time Date: Fri, 19 Aug 2016 19:43:17 +0200 Organization: A noiseless patient Spider Message-ID: References: <7a1eaa60-3be9-4e9b-b67a-d174f3960276@googlegroups.com> <55122d87-53ee-4996-acb2-7d0e2d962eca@googlegroups.com> <3c38b5cc-736b-40ed-bfdd-7df36d0b5c59@googlegroups.com> Reply-To: nonlegitur@futureapps.de Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 7bit Injection-Date: Fri, 19 Aug 2016 17:43:18 -0000 (UTC) Injection-Info: mx02.eternal-september.org; posting-host="b96887e80893c84a90c3007226ca0d1c"; logging-data="22052"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+TbFcCIyMT9OpAih8LbkBooTthQthr+Zk=" User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.9; rv:45.0) Gecko/20100101 Thunderbird/45.2.0 In-Reply-To: <3c38b5cc-736b-40ed-bfdd-7df36d0b5c59@googlegroups.com> Cancel-Lock: sha1:NyuGFCMh6ZcDvLkxOhhsA+9vTUk= Xref: news.eternal-september.org comp.lang.ada:31466 Date: 2016-08-19T19:43:17+02:00 List-Id: 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.)