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." <bauhaus@futureapps.invalid>
Newsgroups: comp.lang.ada
Subject: Re: GNATCOLL SQLite3 vs Ada.Calendar.Time
Date: Thu, 18 Aug 2016 13:19:44 +0200
Organization: A noiseless patient Spider
Message-ID: <np45kg$q1f$1@dont-email.me>
References: <fa1918c3-32cc-4203-bfad-55f26646305f@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: Thu, 18 Aug 2016 11:19:44 -0000 (UTC)
Injection-Info: mx02.eternal-september.org;
 posting-host="b96887e80893c84a90c3007226ca0d1c";
	logging-data="26671"; mail-complaints-to="abuse@eternal-september.org";
	posting-account="U2FsdGVkX19o1eTqiRh+3qMphoUyd0CMt40CiK/AIjY="
User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.11; rv:45.0)
 Gecko/20100101 Thunderbird/45.2.0
In-Reply-To: <fa1918c3-32cc-4203-bfad-55f26646305f@googlegroups.com>
Cancel-Lock: sha1:MIGPjztRUYO6A7WJr1ikXlRs2AA=
Xref: news.eternal-september.org comp.lang.ada:31456
Date: 2016-08-18T13:19:44+02:00
List-Id: <comp.lang.ada>

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(?) ... "

With Ubuntu's standard GNAT, this change results in:

find modified time (1 day before created):
[SQL.SELECT] SELECT ID, Modified, Title FROM Title WHERE Modified >= 
datetime(?) ORDER BY ID, 1999-12-31 23:00:00
1, 2000-01-02 00:00:00, 2001
2, 2000-01-02 00:00:00, Foundation
3, 2000-01-02 00:00:00, Foundations Edge
4, 2000-01-02 00:00:00, Foundation and Earth
5, 2000-01-02 00:00:00, Foundation and Empire

Also, I think that +"some string" has only recently been added
as an overloaded "+" in GNATCOLL.SQL.Exec, in case it's resolved
to be from there and portability to older installation matters.

Testing SQL scripts using standard SQL syntax and several
SQL DBMSs has proven to be useful in detecting caveats.
SQLite's .dump command seems pretty good at producing
standard SQL syntax. (I have had cases where using double
quotes for VARCHAR literals had made the DBMS take "foo"
for column FOO, and that's formally correct.)