Rocksolid Light

Welcome to novaBBS (click a section below)

mail  files  register  newsreader  groups  login

Message-ID:  

Nonsense. Space is blue and birds fly through it. -- Heisenberg


devel / comp.unix.shell / Re: SQL question (actually, sqlite)

SubjectAuthor
* SQL question (actually, sqlite)Kenny McCormack
+* Re: SQL question (actually, sqlite)Josef Moellers
|`* Re: SQL question (actually, sqlite)Kenny McCormack
| +- Re: SQL question (actually, sqlite)Josef Moellers
| `- Re: SQL question (actually, sqlite)Sivaram Neelakantan
`- Re: SQL question (actually, sqlite)Janis Papanagnou

1
SQL question (actually, sqlite)

<sffmr6$tntd$2@news.xmission.com>

 copy mid

https://www.novabbs.com/devel/article-flat.php?id=4234&group=comp.unix.shell#4234

 copy link   Newsgroups: comp.unix.shell
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!xmission!nnrp.xmission!.POSTED.shell.xmission.com!not-for-mail
From: gaze...@shell.xmission.com (Kenny McCormack)
Newsgroups: comp.unix.shell
Subject: SQL question (actually, sqlite)
Date: Tue, 17 Aug 2021 07:00:54 -0000 (UTC)
Organization: The official candy of the new Millennium
Message-ID: <sffmr6$tntd$2@news.xmission.com>
Injection-Date: Tue, 17 Aug 2021 07:00:54 -0000 (UTC)
Injection-Info: news.xmission.com; posting-host="shell.xmission.com:166.70.8.4";
logging-data="974765"; mail-complaints-to="abuse@xmission.com"
X-Newsreader: trn 4.0-test77 (Sep 1, 2010)
Originator: gazelle@shell.xmission.com (Kenny McCormack)
 by: Kenny McCormack - Tue, 17 Aug 2021 07:00 UTC

Yes, it is off-topic, but... I am running this from a shell script, so...

Anyway, I'm hoping someone out there is a SQL expert - well, at least more
knowledgeable than I am (which is a pretty low bar).

I have a table, with a field that contains timestamps (usual Unix epoch
number, which these days is a 10 digit number starting with "16"). I want
to compare it to another, fixed, value (which happens to be the current
time, generated by another script (not written in SQL)). So, I do:

select * from myTable where timestamp > 16xxxxxxxx;

I've found that this always returns true, even when it shouldn't. I can
(and have) fix(ed) it by doing:

select * from myTable where timestamp+0 > 16xxxxxxxx;

which I kind of "random walked" myself into. But I am curious if there is
a better way to solve this. The above, although kind of standard in AWK
(and note that SQL seems to share a lot of ideological similarity with AWK),
still looks like a kludge.

Notes:
1) I didn't do anything special in the "CREATE TABLE" command for this
field. I think there is a way to declare it numeric, but I don't
understand that very well.
2) This is *NOT* an incarnation of the usual "comparing numbers as
strings" problem (where, e.g., 2 sorts above 10), since the things
being compared here are known to be all the same length. So, a
string comparison should work OK.

--
The randomly chosen signature file that would have appeared here is more than 4
lines long. As such, it violates one or more Usenet RFCs. In order to remain
in compliance with said RFCs, the actual sig can be found at the following URL:
http://user.xmission.com/~gazelle/Sigs/RepInsults

Re: SQL question (actually, sqlite)

<io16f9Fo6p1U1@mid.individual.net>

 copy mid

https://www.novabbs.com/devel/article-flat.php?id=4235&group=comp.unix.shell#4235

 copy link   Newsgroups: comp.unix.shell
Path: i2pn2.org!i2pn.org!news.swapon.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: josef.mo...@invalid.invalid (Josef Moellers)
Newsgroups: comp.unix.shell
Subject: Re: SQL question (actually, sqlite)
Date: Tue, 17 Aug 2021 09:23:21 +0200
Lines: 50
Message-ID: <io16f9Fo6p1U1@mid.individual.net>
References: <sffmr6$tntd$2@news.xmission.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
X-Trace: individual.net VvyjFjk0pu/krMTQx46yGATictndoRCX6IrrLRKVMTInx0vKip
Cancel-Lock: sha1:d5yYnoLjENYCgMwUXxaOj8dBQ6g=
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
Thunderbird/78.11.0
In-Reply-To: <sffmr6$tntd$2@news.xmission.com>
Content-Language: en-US
 by: Josef Moellers - Tue, 17 Aug 2021 07:23 UTC

On 17.08.21 09:00, Kenny McCormack wrote:
> Yes, it is off-topic, but... I am running this from a shell script, so...

Well ... almost anything can be run from a shell, so anything could be
asked in this group?
There are numerous groups that have "sql" in their names ...

> Anyway, I'm hoping someone out there is a SQL expert - well, at least more
> knowledgeable than I am (which is a pretty low bar).
>
> I have a table, with a field that contains timestamps (usual Unix epoch
> number, which these days is a 10 digit number starting with "16"). I want
> to compare it to another, fixed, value (which happens to be the current
> time, generated by another script (not written in SQL)). So, I do:
>
> select * from myTable where timestamp > 16xxxxxxxx;

Hm, I have never seen a number "16xxxxxxxx", it just looks strange to
me, even to a German (who swaps "."s and ","s in numbers ;-) )

Have you tried using a "real" number, eg "1600000000" (without the quotes)?

> I've found that this always returns true, even when it shouldn't. I can
> (and have) fix(ed) it by doing:
>
> select * from myTable where timestamp+0 > 16xxxxxxxx;

I gues this tries to fix it from the wrong side. It might work if
"timestamp" is a string, but not if "timestamp" is already a number.

> which I kind of "random walked" myself into. But I am curious if there is
> a better way to solve this. The above, although kind of standard in AWK
> (and note that SQL seems to share a lot of ideological similarity with AWK),
> still looks like a kludge.
>
> Notes:
> 1) I didn't do anything special in the "CREATE TABLE" command for this
> field. I think there is a way to declare it numeric, but I don't
> understand that very well.
> 2) This is *NOT* an incarnation of the usual "comparing numbers as
> strings" problem (where, e.g., 2 sorts above 10), since the things
> being compared here are known to be all the same length. So, a
> string comparison should work OK.

Why do you think you MUST compare strings rather than numbers?

If the above doesn't help: what does your "CREATE TABLE" statement look
like?

Josef

Re: SQL question (actually, sqlite)

<sffp58$tpaa$1@news.xmission.com>

 copy mid

https://www.novabbs.com/devel/article-flat.php?id=4236&group=comp.unix.shell#4236

 copy link   Newsgroups: comp.unix.shell
Path: i2pn2.org!i2pn.org!weretis.net!feeder6.news.weretis.net!xmission!nnrp.xmission!.POSTED.shell.xmission.com!not-for-mail
From: gaze...@shell.xmission.com (Kenny McCormack)
Newsgroups: comp.unix.shell
Subject: Re: SQL question (actually, sqlite)
Date: Tue, 17 Aug 2021 07:40:24 -0000 (UTC)
Organization: The official candy of the new Millennium
Message-ID: <sffp58$tpaa$1@news.xmission.com>
References: <sffmr6$tntd$2@news.xmission.com> <io16f9Fo6p1U1@mid.individual.net>
Injection-Date: Tue, 17 Aug 2021 07:40:24 -0000 (UTC)
Injection-Info: news.xmission.com; posting-host="shell.xmission.com:166.70.8.4";
logging-data="976202"; mail-complaints-to="abuse@xmission.com"
X-Newsreader: trn 4.0-test77 (Sep 1, 2010)
Originator: gazelle@shell.xmission.com (Kenny McCormack)
 by: Kenny McCormack - Tue, 17 Aug 2021 07:40 UTC

In article <io16f9Fo6p1U1@mid.individual.net>,
Josef Moellers <josef.moellers@invalid.invalid> wrote:
....
>> select * from myTable where timestamp > 16xxxxxxxx;
>
>Hm, I have never seen a number "16xxxxxxxx", it just looks strange to
>me, even to a German (who swaps "."s and ","s in numbers ;-) )

I am using an actual number (an integer, though, not a "real number" - yes,
I am kidding...). The Xs were just standins.

>> I've found that this always returns true, even when it shouldn't. I can
>> (and have) fix(ed) it by doing:
>>
>> select * from myTable where timestamp+0 > 16xxxxxxxx;
>
>I gues this tries to fix it from the wrong side. It might work if
>"timestamp" is a string, but not if "timestamp" is already a number.

Note sure what you mean by this.

>Why do you think you MUST compare strings rather than numbers?

I don't. The point is I want them to compare as numbers. Hence the +0.

I'm just pointing out that it "should" work even if they were being compared
as strings.

>If the above doesn't help: what does your "CREATE TABLE" statement look
>like?

Just: CREATE TABLE xxx foo,bar,timestamp;

Anyway, thanx for response. Hope to see more from you.

--
Adderall, pseudoephed, teleprompter

Re: SQL question (actually, sqlite)

<io1gd7Fq1j9U1@mid.individual.net>

 copy mid

https://www.novabbs.com/devel/article-flat.php?id=4237&group=comp.unix.shell#4237

 copy link   Newsgroups: comp.unix.shell
Path: i2pn2.org!i2pn.org!news.swapon.de!fu-berlin.de!uni-berlin.de!individual.net!not-for-mail
From: josef.mo...@invalid.invalid (Josef Moellers)
Newsgroups: comp.unix.shell
Subject: Re: SQL question (actually, sqlite)
Date: Tue, 17 Aug 2021 12:12:55 +0200
Lines: 87
Message-ID: <io1gd7Fq1j9U1@mid.individual.net>
References: <sffmr6$tntd$2@news.xmission.com>
<io16f9Fo6p1U1@mid.individual.net> <sffp58$tpaa$1@news.xmission.com>
Mime-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
X-Trace: individual.net +gOfIq6oFGn5rlMQYqFQfQ2+sIf1DQUBLxIb2kD9pwzUm6fPxW
Cancel-Lock: sha1:pqxV81Xkh2btF7ecOa7oA8TNit4=
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101
Thunderbird/78.11.0
In-Reply-To: <sffp58$tpaa$1@news.xmission.com>
Content-Language: en-US
 by: Josef Moellers - Tue, 17 Aug 2021 10:12 UTC

For one, maybe this should better go to comp.databases.mysql?

On 17.08.21 09:40, Kenny McCormack wrote:
> In article <io16f9Fo6p1U1@mid.individual.net>,
> Josef Moellers <josef.moellers@invalid.invalid> wrote:
> ...
>>> select * from myTable where timestamp > 16xxxxxxxx;
>>
>> Hm, I have never seen a number "16xxxxxxxx", it just looks strange to
>> me, even to a German (who swaps "."s and ","s in numbers ;-) )
>
> I am using an actual number (an integer, though, not a "real number" - yes,
> I am kidding...). The Xs were just standins.

It is ALWAYS better to be as precise as possible. We just don't know
what you /actually/ mean when you write something. So just write the
EXACT statement you useot are using rather than something that resembles
whatever you wrote. I guess you won't tell any secrets if you write the
exact number you are comparing with.

>>> I've found that this always returns true, even when it shouldn't. I can
>>> (and have) fix(ed) it by doing:
>>>
>>> select * from myTable where timestamp+0 > 16xxxxxxxx;
>>
>> I gues this tries to fix it from the wrong side. It might work if
>> "timestamp" is a string, but not if "timestamp" is already a number.
>
> Note sure what you mean by this.

I'm not an SQL expert myself, but in a number of languages and contexts,
adding "0" to a STRING (or multiplying a STRING with a "1") containing a
number converts the STRING into a proper NUMBER which you then can
compare properly.
So, if this were one of these contexts (again: IANASE), adding "0" to
something that is already a NUMBER (rather than a STRING) will do nothing.

>> Why do you think you MUST compare strings rather than numbers?
>
> I don't. The point is I want them to compare as numbers. Hence the +0.
>
> I'm just pointing out that it "should" work even if they were being compared
> as strings.
>
>> If the above doesn't help: what does your "CREATE TABLE" statement look
>> like?
>
> Just: CREATE TABLE xxx foo,bar,timestamp;

This does not work. If you want help, please QUOTE the EXACT statement
and not something you think will tell what you actually did.

I tried this:

CREATE TABLE xxx (foo,bar,timestamp);
INSERT INTO xxx (foo,bar,timestamp) VALUES ("abc", "devf", 12345);
SELECT * FROM xxx WHERE timestamp > 10000;
abc|devf|12345
SELECT * FROM xxx WHERE timestamp > 20000;
<no output>
SELECT * FROM xxx WHERE timestamp > "10000";
<no output>
SELECT * FROM xxx WHERE foo > "aaa";
abc|devf|12345
SELECT * FROM xxx WHERE foo > "bbb";
<no output>
INSERT INTO xxx (foo,bar,timestamp) VALUES ("ghi", "jklm", "12345");
SELECT * FROM xxx WHERE timestamp > 10000;
abc|devf|12345
ghi|jklm|12345
SELECT * FROM xxx WHERE timestamp > 20000;
ghi|jklm|12345
SELECT * FROM xxx WHERE timestamp > "10000";
ghi|jklm|12345
SELECT * FROM xxx WHERE timestamp > "20000";
<no output>

So, you should check your CREATE TABLE statement for the datatype you
use for "timestamp" and the INSERT statement for the datatype you use
for the value inserted into the "timestamp" field.

> Anyway, thanx for response. Hope to see more from you.

Again: please write EXACTLY what you did and NOT what you think you
might want to tell us.

Josef

Re: SQL question (actually, sqlite)

<85im04vxgm.fsf@gmail.com>

 copy mid

https://www.novabbs.com/devel/article-flat.php?id=4243&group=comp.unix.shell#4243

 copy link   Newsgroups: comp.unix.shell
Path: i2pn2.org!i2pn.org!eternal-september.org!reader02.eternal-september.org!.POSTED!not-for-mail
From: nsivaram...@gmail.com (Sivaram Neelakantan)
Newsgroups: comp.unix.shell
Subject: Re: SQL question (actually, sqlite)
Date: Tue, 17 Aug 2021 22:07:45 +0530
Organization: A noiseless patient Spider
Lines: 45
Message-ID: <85im04vxgm.fsf@gmail.com>
References: <sffmr6$tntd$2@news.xmission.com>
<io16f9Fo6p1U1@mid.individual.net> <sffp58$tpaa$1@news.xmission.com>
Mime-Version: 1.0
Content-Type: text/plain
Injection-Info: reader02.eternal-september.org; posting-host="627111b8bd9772a0f78e907048e166aa";
logging-data="27103"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX182ugfjb+I3FkJ8Bnp9onk0dLQxjIhdSlE="
User-Agent: Gnus/5.13 (Gnus v5.13) Emacs/27.2 (windows-nt)
Cancel-Lock: sha1:ZyqAs5F+rcNdFFwosiVmtYNh55g=
sha1:EYC6eKNxdtCeYi11D/v8HawHBC0=
User-Mail-Address: nsivaram.net@gmail.com
 by: Sivaram Neelakantan - Tue, 17 Aug 2021 16:37 UTC

On Tue, Aug 17 2021,Kenny McCormack wrote:

[snipped 30 lines]

>
> Just: CREATE TABLE xxx foo,bar,timestamp;
>
> Anyway, thanx for response. Hope to see more from you.

Could you try and post the following output as Josef suggested

A random example from within sqlite is shown below; one or the other
should be fine.

sqlite> .tables
proglang_tbluk
sqlite> .schema proglang_tbluk
CREATE TABLE proglang_tbluk (
id INTEGER NOT NULL PRIMARY KEY,
language VARCHAR(20) NOT NULL UNIQUE,
author VARCHAR(25) NOT NULL,
year INTEGER NOT NULL,
standard VARCHAR(10) NULL, current_status VARCHAR(32) NULL);
sqlite> .header on
sqlite> .mode column
sqlite> pragma table_info('proglang_tbluk');
cid name type notnull dflt_value pk
---------- ---------- ---------- ---------- ---------- ----------
0 id INTEGER 1 1
1 language VARCHAR(20 1 0
2 author VARCHAR(25 1 0
3 year INTEGER 1 0
4 standard VARCHAR(10 0 0
5 current_st VARCHAR(32 0 0
sqlite>

And a perusal of this part especially sec 4.2 might help, if you
haven't already done so.

https://www.sqlite.org/datatype3.html

sivaram
--

Re: SQL question (actually, sqlite)

<sfgtgr$m34$1@news-1.m-online.net>

 copy mid

https://www.novabbs.com/devel/article-flat.php?id=4244&group=comp.unix.shell#4244

 copy link   Newsgroups: comp.unix.shell
Path: i2pn2.org!i2pn.org!weretis.net!feeder8.news.weretis.net!news.szaf.org!news.karotte.org!news.space.net!news.m-online.net!.POSTED!not-for-mail
From: janis_pa...@hotmail.com (Janis Papanagnou)
Newsgroups: comp.unix.shell
Subject: Re: SQL question (actually, sqlite)
Date: Tue, 17 Aug 2021 20:00:59 +0200
Organization: (posted via) M-net Telekommunikations GmbH
Lines: 64
Message-ID: <sfgtgr$m34$1@news-1.m-online.net>
References: <sffmr6$tntd$2@news.xmission.com>
NNTP-Posting-Host: 2001:a61:241e:cc01:60c9:9531:2f0e:b4eb
Mime-Version: 1.0
Content-Type: text/plain; charset=windows-1252
Content-Transfer-Encoding: 7bit
X-Trace: news-1.m-online.net 1629223259 22628 2001:a61:241e:cc01:60c9:9531:2f0e:b4eb (17 Aug 2021 18:00:59 GMT)
X-Complaints-To: news@news-1.m-online.net
NNTP-Posting-Date: Tue, 17 Aug 2021 18:00:59 +0000 (UTC)
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:45.0) Gecko/20100101
Thunderbird/45.8.0
X-Enigmail-Draft-Status: N1110
In-Reply-To: <sffmr6$tntd$2@news.xmission.com>
 by: Janis Papanagnou - Tue, 17 Aug 2021 18:00 UTC

On 17.08.2021 09:00, Kenny McCormack wrote:
> Yes, it is off-topic, but... I am running this from a shell script, so...
>
> Anyway, I'm hoping someone out there is a SQL expert - well, at least more
> knowledgeable than I am (which is a pretty low bar).
>
> I have a table, with a field that contains timestamps (usual Unix epoch
> number, which these days is a 10 digit number starting with "16"). I want
> to compare it to another, fixed, value (which happens to be the current
> time, generated by another script (not written in SQL)). So, I do:
>
> select * from myTable where timestamp > 16xxxxxxxx;
>
> I've found that this always returns true, even when it shouldn't. I can
> (and have) fix(ed) it by doing:
>
> select * from myTable where timestamp+0 > 16xxxxxxxx;
>
> which I kind of "random walked" myself into. But I am curious if there is
> a better way to solve this. The above, although kind of standard in AWK
> (and note that SQL seems to share a lot of ideological similarity with AWK),
> still looks like a kludge.
>
> Notes:
> 1) I didn't do anything special in the "CREATE TABLE" command for this
> field. I think there is a way to declare it numeric, but I don't
> understand that very well.
> 2) This is *NOT* an incarnation of the usual "comparing numbers as
> strings" problem (where, e.g., 2 sorts above 10), since the things
> being compared here are known to be all the same length. So, a
> string comparison should work OK.
>

Well, yet it doesn't seem you have contributed any substantial
information to help tracking your issue.

I'm also no SQL/sqlite expert or anything, but it's quite simple
to set up a DB, fill it with numeric data, and select or sort the
result, to see that all works as expected.

To track your problem you have to cooperate.

Provide the issued commands and output that you got, tell us why
the output isn't what you expected. Sivaram showed how to inspect
the column definitions.

.mode column
.header on
pragma table_info('myTable');

All that is useful information and the returned results may help.

Depending on the size of the DB I would probably start with a full
SELECT * FROM myTable;
Then continue step by step
SELECT * FROM myTable WHERE timestamp > 1600000000;
SELECT * FROM myTable WHERE timestamp > 1600000000 and timestamp <
1700000000;
SELECT * FROM myTable WHERE timestamp > 1600000000 and timestamp <
1700000000 ORDER BY timestamp;

Janis

1
server_pubkey.txt

rocksolid light 0.9.7
clearnet tor