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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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
--

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

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

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)