[sqlite] Can't understand why I need this cast
The '+ - * / ' operator trying to convert text to numeric, so
typeof(strftime('%s', starttime)-(180*60)) -> integer
typeof('1') -> text
typeof('1' + 0) -> integer
typeof( 1 || 0 ) -> text
???: Rob Willett<mailto:rob.sqlite at robertwillett.com>
: ?2016/?2/?18 20:16
???: SQLite mailing list<mailto:sqlite-users at mailinglists.sqlite.org>
??: Re: [sqlite] Can't understand why I need this cast
Quad,
Thanks, that almost makes sense. What still confuses us is that the
other maths expressions work OK without a cast.
e.g.
strftime('%s' , starttime) - (180 * 60)
and
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
and
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
all work but
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
doesn?t.
So why does
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
work and
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
not work.
Thanks,
Rob
On 18 Feb 2016, at 11:25, Quan Yong Zhai wrote:
> It looks like strftime('%s',...) should return an integer and
> strftime('%J', ...) should return a double value. But it always return
> text value.
>
> ???: Rob Willett<mailto:rob.sqlite at robertwillett.com>
> : ?2016/?2/?18 18:20
> ???: SQLite mailing
> list<mailto:sqlite-users at mailinglists.sqlite.org>
> ??: [sqlite] Can't understand why I need this cast
>
> Hi,
>
> I?m sure this is a really dumb question but I?m struggling to
> understand why the following SQL is needed for what should be a
> trivial
> SQL expression.
>
> I?ve minimised the example down to (hopefully) make it simpler.
>
> I have a table with an ISO date StartTime in it held as a string.
>
>
> sqlite> .schema test1
> CREATE TABLE "Test1" (
> "Id" INTEGER NOT NULL,
> "StartTime" TEXT NOT NULL,
> PRIMARY KEY("Id")
> );
>
>
>
> I add in some test ISO date data that *looks* OK
>
>
> sqlite> select * from test1;
> 1|2011-05-03T05:00:00Z
>
>
> I run the following to check that the ISO date is between a range of
> seconds, i.e. its greater than three hours ago and less than three
> hours
> in the future. I know this is contrived but this is the minimum test
> case from a far larger query.
>
> select
> strftime('%s' , starttime) ,
> strftime('%s' , starttime) - (180 * 60) ,
> strftime('%s' , starttime) + (180 * 60) ,
> strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 *
> 60))
> ,
> ,
> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180
> * 60))
> ,
> strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180
> * 60))
> from
> test1;
>
> What I expect to see is
>
> 1304398800|1304388000|1304409600|10800|10800|1|0
>
> 1. the ISO start time as secs - Correct
> 2. The ISO start time in secs minus 10800 - Correct
> 3, The ISO start time in secs plus 10800 - Correct
> 4. The value 10800 to check that the maths is correct - Correct
> 5. The value 10800 to check that the maths is correct - Correct
> 6. A check that the ISO value in secs is greater than the current time
> minus 10800 - Correct
> 7. A check that the ISO value in secs is less than the current time
> plus
> 10800 secs - INCORRECT
>
> if I CAST the value of the last column
>
> select
> strftime('%s' , starttime) ,
> strftime('%s' , starttime) - (180 * 60) ,
> strftime('%s' , starttime) + (180 * 60) ,
> strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 *
> 60))
> ,
> (strftime('%s' , starttime) + (180 * 60)) - strftime('%s' ,
> starttime)
> ,
> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180
> * 60))
> ,
> strftime('%s' , starttime) <= cast((strftime('%s' , starttime) +
> (180 *
> 60)) as integer)
> from
> test1;
>
> I get the right answer
>
> 1304398800|1304388000|1304409600|10800|10800|1|1
>
> I?m puzzled as to why this should be. Why would I need to cast an
> integer to an integer. I??m sure there?s an obvious answer but I
> can?t find it.
>
> Any suggestions please?
>
> Thanks,
>
> Rob
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can't understand why I need this cast
It looks like strftime('%s',...) should return an integer and strftime('%J',
...) should return a double value. But it always return text value.
???: Rob Willett<mailto:rob.sqlite at robertwillett.com>
: ?2016/?2/?18 18:20
???: SQLite mailing list<mailto:sqlite-users at mailinglists.sqlite.org>
??: [sqlite] Can't understand why I need this cast
Hi,
I?m sure this is a really dumb question but I?m struggling to
understand why the following SQL is needed for what should be a trivial
SQL expression.
I?ve minimised the example down to (hopefully) make it simpler.
I have a table with an ISO date StartTime in it held as a string.
sqlite> .schema test1
CREATE TABLE "Test1" (
"Id" INTEGER NOT NULL,
"StartTime" TEXT NOT NULL,
PRIMARY KEY("Id")
);
I add in some test ISO date data that *looks* OK
sqlite> select * from test1;
1|2011-05-03T05:00:00Z
I run the following to check that the ISO date is between a range of
seconds, i.e. its greater than three hours ago and less than three hours
in the future. I know this is contrived but this is the minimum test
case from a far larger query.
select
strftime('%s' , starttime) ,
strftime('%s' , starttime) - (180 * 60) ,
strftime('%s' , starttime) + (180 * 60) ,
strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60))
,
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
,
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
from
test1;
What I expect to see is
1304398800|1304388000|1304409600|10800|10800|1|0
1. the ISO start time as secs - Correct
2. The ISO start time in secs minus 10800 - Correct
3, The ISO start time in secs plus 10800 - Correct
4. The value 10800 to check that the maths is correct - Correct
5. The value 10800 to check that the maths is correct - Correct
6. A check that the ISO value in secs is greater than the current time
minus 10800 - Correct
7. A check that the ISO value in secs is less than the current time plus
10800 secs - INCORRECT
if I CAST the value of the last column
select
strftime('%s' , starttime) ,
strftime('%s' , starttime) - (180 * 60) ,
strftime('%s' , starttime) + (180 * 60) ,
strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60))
,
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
,
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
strftime('%s' , starttime) <= cast((strftime('%s' , starttime) + (180 *
60)) as integer)
from
test1;
I get the right answer
1304398800|1304388000|1304409600|10800|10800|1|1
I?m puzzled as to why this should be. Why would I need to cast an
integer to an integer. I??m sure there?s an obvious answer but I
can?t find it.
Any suggestions please?
Thanks,
Rob
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can't understand why I need this cast
sqlite>Select typeof(strftime('%s','now'));
text
sqlite>select typeof('1' + 1);
integer
???: Rob Willett<mailto:rob.sqlite at robertwillett.com>
: ?2016/?2/?18 18:20
???: SQLite mailing list<mailto:sqlite-users at mailinglists.sqlite.org>
??: [sqlite] Can't understand why I need this cast
Hi,
I?m sure this is a really dumb question but I?m struggling to
understand why the following SQL is needed for what should be a trivial
SQL expression.
I?ve minimised the example down to (hopefully) make it simpler.
I have a table with an ISO date StartTime in it held as a string.
sqlite> .schema test1
CREATE TABLE "Test1" (
"Id" INTEGER NOT NULL,
"StartTime" TEXT NOT NULL,
PRIMARY KEY("Id")
);
I add in some test ISO date data that *looks* OK
sqlite> select * from test1;
1|2011-05-03T05:00:00Z
I run the following to check that the ISO date is between a range of
seconds, i.e. its greater than three hours ago and less than three hours
in the future. I know this is contrived but this is the minimum test
case from a far larger query.
select
strftime('%s' , starttime) ,
strftime('%s' , starttime) - (180 * 60) ,
strftime('%s' , starttime) + (180 * 60) ,
strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60))
,
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
,
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
from
test1;
What I expect to see is
1304398800|1304388000|1304409600|10800|10800|1|0
1. the ISO start time as secs - Correct
2. The ISO start time in secs minus 10800 - Correct
3, The ISO start time in secs plus 10800 - Correct
4. The value 10800 to check that the maths is correct - Correct
5. The value 10800 to check that the maths is correct - Correct
6. A check that the ISO value in secs is greater than the current time
minus 10800 - Correct
7. A check that the ISO value in secs is less than the current time plus
10800 secs - INCORRECT
if I CAST the value of the last column
select
strftime('%s' , starttime) ,
strftime('%s' , starttime) - (180 * 60) ,
strftime('%s' , starttime) + (180 * 60) ,
strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60))
,
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
,
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
strftime('%s' , starttime) <= cast((strftime('%s' , starttime) + (180 *
60)) as integer)
from
test1;
I get the right answer
1304398800|1304388000|1304409600|10800|10800|1|1
I?m puzzled as to why this should be. Why would I need to cast an
integer to an integer. I??m sure there?s an obvious answer but I
can?t find it.
Any suggestions please?
Thanks,
Rob
___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can't understand why I need this cast
On 18 February 2016 at 15:29, Richard Hipp wrote: > On 2/18/16, Simon Davies wrote: >> > > The documentation says: "An expression of the form "CAST(expr AS > type)" has an affinity thta is the same as a column with a declared > type of "type".". Since the RHS of the <= operator now has affinity, > that affinity is applied to the LHS operand as well, resulting in both > operands being treated as integers for the purpose of the comparison. > -- > D. Richard Hipp I was aware that CAST produces affinity as quoted, but not that a literal integer does not... What is the affinity of a literal integer? I have had a quick search through the documentation but did not find anything. Regards, Simon
[sqlite] Can't understand why I need this cast
On 18 February 2016 at 12:34, Richard Hipp wrote: > On 2/18/16, Simon Davies wrote: >> sqlite> select '11' <= 11; >> 0 >> sqlite> select '11' <= cast(11 as integer); >> 1 >> sqlite> select '11' >= 11; >> 1 > > Sort order in SQLite is: (1) NULLs, (2) Numeric values in numeric > order, (3) Strings in the order of whatever collating sequence > applies, and (4) BLOBs in lexicographical order. > > Hence, strings are always greater than numbers. Always. > > If you want to compare a string against it's numeric equivalent, you > need to cast the string to a number. > > -- > D. Richard Hipp Ok on sort order, but why SQLite version 3.8.11.1 2015-07-29 20:00:57 Enter ".help" for usage hints. Connected to a transient in-memory database. Use ".open FILENAME" to reopen on a persistent database. sqlite> sqlite> select '11' <= 11; 0 sqlite> select '11' <= cast(11 as integer); 1 Regards, Simon
[sqlite] Can't understand why I need this cast
Consider
asql> select '1' >= 1, '1' <= 1;
'1' >= 1 '1' <= 1
1 0
This is because of the implicit ordering of TEXT and INTEGER values.
Strftime() returns a text; in the expression "strftime() + 1" the arithmetic
operator "casts" its left hand operand to integer (actually: applies numeric
affinity) because SQLite is trying to be nice instead of complaining about a
type mismatch and yields something numeric. The relational operator does not
cast ist operands in that way.
-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Rob
Willett
Gesendet: Donnerstag, 18. Februar 2016 13:16
An: SQLite mailing list
Betreff: Re: [sqlite] Can't understand why I need this cast
Quad,
Thanks, that almost makes sense. What still confuses us is that the other maths
expressions work OK without a cast.
e.g.
strftime('%s' , starttime) - (180 * 60)
and
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
and
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60)) ,
all work but
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
doesn?t.
So why does
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
work and
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
not work.
Thanks,
Rob
On 18 Feb 2016, at 11:25, Quan Yong Zhai wrote:
> It looks like strftime('%s',...) should return an integer and
> strftime('%J', ...) should return a double value. But it always return
> text value.
>
> ???: Rob Willett<mailto:rob.sqlite at robertwillett.com>
> : ?2016/?2/?18 18:20
> ???: SQLite mailing
> list<mailto:sqlite-users at mailinglists.sqlite.org>
> ??: [sqlite] Can't understand why I need this cast
>
> Hi,
>
> I?m sure this is a really dumb question but I?m struggling to
> understand why the following SQL is needed for what should be a
> trivial SQL expression.
>
> I?ve minimised the example down to (hopefully) make it simpler.
>
> I have a table with an ISO date StartTime in it held as a string.
>
>
> sqlite> .schema test1
> CREATE TABLE "Test1" (
> "Id" INTEGER NOT NULL,
> "StartTime" TEXT NOT NULL,
> PRIMARY KEY("Id")
> );
>
>
>
> I add in some test ISO date data that *looks* OK
>
>
> sqlite> select * from test1;
> 1|2011-05-03T05:00:00Z
>
>
> I run the following to check that the ISO date is between a range of
> seconds, i.e. its greater than three hours ago and less than three
> hours in the future. I know this is contrived but this is the minimum
> test case from a far larger query.
>
> select
> strftime('%s' , starttime) ,
> strftime('%s' , starttime) - (180 * 60) ,
> strftime('%s' , starttime) + (180 * 60) ,
> strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 *
> 60))
> ,
> ,
> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180
> * 60))
> ,
> strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180
> * 60))
> from
> test1;
>
> What I expect to see is
>
> 1304398800|1304388000|1304409600|10800|10800|1|0
>
> 1. the ISO start time as secs - Correct 2. The ISO start time in secs
> minus 10800 - Correct 3, The ISO start time in secs plus 10800 -
> Correct 4. The value 10800 to check that the maths is correct -
> Correct 5. The value 10800 to check that the maths is correct -
> Correct 6. A check that the ISO value in secs is greater than the
> current time minus 10800 - Correct 7. A check that the ISO value in
> secs is less than the current time plus
> 10800 secs - INCORRECT
>
> if I CAST the value of the last column
>
> select
> strftime('%s' , starttime) ,
> strftime('%s' , starttime) - (180 * 60) ,
> strftime('%s' , starttime) + (180 * 60) ,
> strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 *
> 60))
> ,
> (strftime('%s' , starttime) + (180 * 60)) - strftime('%s' ,
> starttime)
> ,
> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180
> * 60))
> ,
> strftime('%s' , starttime) <= cast((strftime('%s' , starttime) +
> (180 *
> 60)) as integer)
> from
> test1;
>
> I get the right answer
>
> 1304398800|1304388000|1304409600|10800|10800|1|1
>
> I?m puzzled as to why this should be. Why would I need to cast an
> integer to an integer. I??m sure there?s an obvious answer but I can?t
> find it.
>
> Any suggestions please?
>
> Thanks,
>
> Rob
> _
[sqlite] Can't understand why I need this cast
Richard,
The example is highly contrived. The actual SQL we are/were actually
interested in is
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60)),
AND
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
The rest of the SQL was to test what was happening with strftime and
whether integers being produced from the expressions to test the two SQL
statements above. We thought the contrived test expressions worked OK
which was why we were puzzled as to why
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60)),
worked and
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
failed, though the only difference was the change in the boolean
operand.
As Tim Streater pointed out we would actually use epoch seconds as this
is more natural and we have that information in another database.
However we threw/hacked the above SQL together to test an idea we had,
specifically to find traffic incidents three hours before and three
hours after other traffic incidents.The test database table we tested it
with didn?t have the epoch seconds in, so we used strftime instead to
check our idea. Thats when we got results that puzzled us, we know we
have alternate and better solutions to actually doing the work, whats
worrying us is that our understanding of SQLite, strftime and implicit
cast conversions is incorrect. The fact that strftime returns a text
string is fine and that fits in with what we expected. We simple got the
rest of the conversions wrong. We appear to have created a boolean
expression that worked (a false positive) and that threw us back as we
misunderstood (quite badly) what was going on.
We now know we have to be more careful about how we use strftime and we
will be in our logic.
Thanks to everybody for the help, we?ve learnt something new today
which is always good.
Best wishes,
Rob
On 18 Feb 2016, at 12:45, Richard Hipp wrote:
> On 2/18/16, Rob Willett wrote:
>>
>> select
>> strftime('%s' , starttime),
>> strftime('%s' , starttime) - (180 * 60),
>>strftime('%s' , starttime) + (180 * 60),
>> strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 *
>> 60)),
>> (strftime('%s' , starttime) + (180 * 60)) - strftime('%s' ,
>> starttime),
>> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 *
>> 60)),
>> strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 *
>> 60))
>> from
>> test1;
>>
>
> I don't exactly understand why you are doing the query above, but for
> whatever your purpose is, the following formulation seems simpler to
> me:
>
> SELECT
> strftime('%s', starttime),
> strftime('%s', starttime, '-180 seconds'),
> strftime('%s', starttime, '+180 seconds'),
> (julianday(starttime) - julianday(starttime','-180 seconds'))*86400,
> (julianday(starttime, '+180 seconds') - julianday(starttime))*86400,
> julianday(starttime) >= julianday(starttime, '-180 seconds'),
> julianday(starttime) <= julianday(starttime, '+180 seconds')
> FROM
> test1;
>
>
>
>
> --
> D. Richard Hipp
> drh at sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can't understand why I need this cast
sqlite> select '11' <= 11;
0
sqlite> select '11' <= cast(11 as integer);
1
sqlite> select '11' >= 11;
1
Certainly seems odd...
On 18 February 2016 at 12:20, Rob Willett
wrote:
> Tim,
>
> We actually do store the timestamps as epoch seconds, but we wrote a quick
> piece of SQL to test something out and wanted to use the ISO date. Thats
> when the SQL failed and we couldn?t understand why. We hate it when we don?t
> understand why things don?t work the way we expect. Our OCD kicks in and
> annoys us :)
>
> We?ve investigated it further and it still makes no sense, though Quan Yong
> Zhai has helped. It appears that
>
> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
>
> works BUT
>
> strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
>
> does not work.
>
> The only difference is the boolean operand.
>
> We know how to solve the problem, we?re puzzled though as our understanding
> is clearly wrong.
>
> Thanks for replying,
>
> Rob
>
> On 18 Feb 2016, at 12:14, Tim Streater wrote:
>
>> On 18 Feb 2016 at 10:20, Rob Willett wrote:
>>
>>> I?m sure this is a really dumb question but I?m struggling to
>>> understand why the following SQL is needed for what should be a trivial
>>> SQL expression.
>>>
>>> I?ve minimised the example down to (hopefully) make it simpler.
>>>
>>> I have a table with an ISO date StartTime in it held as a string.
>>
>>
>> I'd be inclined to store your dates as seconds since the epoch. That way
>> arithmetic and comparisons become easy, and your SQL looks simpler. Convert
>> to a string for display. But perhaps your application prevents that for some
>> reason.
>>
>> --
>> Cheers -- Tim
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can't understand why I need this cast
On 18 Feb 2016, at 12:20pm, Rob Willett wrote:
> We know how to solve the problem, we?re puzzled though as our understanding
> is clearly wrong.
It'll help to test the function
typeof(value)
on the values you're handling like
typeof(strftime('%s' , starttime))
to see what's happening.
Simon.
[sqlite] Can't understand why I need this cast
Tim,
We actually do store the timestamps as epoch seconds, but we wrote a
quick piece of SQL to test something out and wanted to use the ISO date.
Thats when the SQL failed and we couldn?t understand why. We hate it
when we don?t understand why things don?t work the way we expect.
Our OCD kicks in and annoys us :)
We?ve investigated it further and it still makes no sense, though Quan
Yong Zhai has helped. It appears that
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
works BUT
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
does not work.
The only difference is the boolean operand.
We know how to solve the problem, we?re puzzled though as our
understanding is clearly wrong.
Thanks for replying,
Rob
On 18 Feb 2016, at 12:14, Tim Streater wrote:
> On 18 Feb 2016 at 10:20, Rob Willett
> wrote:
>
>> I?m sure this is a really dumb question but I?m struggling to
>> understand why the following SQL is needed for what should be a
>> trivial
>> SQL expression.
>>
>> I?ve minimised the example down to (hopefully) make it simpler.
>>
>> I have a table with an ISO date StartTime in it held as a string.
>
> I'd be inclined to store your dates as seconds since the epoch. That
> way arithmetic and comparisons become easy, and your SQL looks
> simpler. Convert to a string for display. But perhaps your application
> prevents that for some reason.
>
> --
> Cheers -- Tim
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can't understand why I need this cast
Quad,
Thanks, that almost makes sense. What still confuses us is that the
other maths expressions work OK without a cast.
e.g.
strftime('%s' , starttime) - (180 * 60)
and
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
and
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
all work but
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
doesn?t.
So why does
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
work and
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
not work.
Thanks,
Rob
On 18 Feb 2016, at 11:25, Quan Yong Zhai wrote:
> It looks like strftime('%s',...) should return an integer and
> strftime('%J', ...) should return a double value. But it always return
> text value.
>
> ???: Rob Willett<mailto:rob.sqlite at robertwillett.com>
> : ?2016/?2/?18 18:20
> ???: SQLite mailing
> list<mailto:sqlite-users at mailinglists.sqlite.org>
> ??: [sqlite] Can't understand why I need this cast
>
> Hi,
>
> I?m sure this is a really dumb question but I?m struggling to
> understand why the following SQL is needed for what should be a
> trivial
> SQL expression.
>
> I?ve minimised the example down to (hopefully) make it simpler.
>
> I have a table with an ISO date StartTime in it held as a string.
>
>
> sqlite> .schema test1
> CREATE TABLE "Test1" (
> "Id" INTEGER NOT NULL,
> "StartTime" TEXT NOT NULL,
> PRIMARY KEY("Id")
> );
>
>
>
> I add in some test ISO date data that *looks* OK
>
>
> sqlite> select * from test1;
> 1|2011-05-03T05:00:00Z
>
>
> I run the following to check that the ISO date is between a range of
> seconds, i.e. its greater than three hours ago and less than three
> hours
> in the future. I know this is contrived but this is the minimum test
> case from a far larger query.
>
> select
> strftime('%s' , starttime) ,
> strftime('%s' , starttime) - (180 * 60) ,
> strftime('%s' , starttime) + (180 * 60) ,
> strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 *
> 60))
> ,
> ,
> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180
> * 60))
> ,
> strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180
> * 60))
> from
> test1;
>
> What I expect to see is
>
> 1304398800|1304388000|1304409600|10800|10800|1|0
>
> 1. the ISO start time as secs - Correct
> 2. The ISO start time in secs minus 10800 - Correct
> 3, The ISO start time in secs plus 10800 - Correct
> 4. The value 10800 to check that the maths is correct - Correct
> 5. The value 10800 to check that the maths is correct - Correct
> 6. A check that the ISO value in secs is greater than the current time
> minus 10800 - Correct
> 7. A check that the ISO value in secs is less than the current time
> plus
> 10800 secs - INCORRECT
>
> if I CAST the value of the last column
>
> select
> strftime('%s' , starttime) ,
> strftime('%s' , starttime) - (180 * 60) ,
> strftime('%s' , starttime) + (180 * 60) ,
> strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 *
> 60))
> ,
> (strftime('%s' , starttime) + (180 * 60)) - strftime('%s' ,
> starttime)
> ,
> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180
> * 60))
> ,
> strftime('%s' , starttime) <= cast((strftime('%s' , starttime) +
> (180 *
> 60)) as integer)
> from
> test1;
>
> I get the right answer
>
> 1304398800|1304388000|1304409600|10800|10800|1|1
>
> I?m puzzled as to why this should be. Why would I need to cast an
> integer to an integer. I??m sure there?s an obvious answer but I
> can?t find it.
>
> Any suggestions please?
>
> Thanks,
>
> Rob
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can't understand why I need this cast
On 18 Feb 2016 at 10:20, Rob Willett wrote: > I?m sure this is a really dumb question but I?m struggling to > understand why the following SQL is needed for what should be a trivial > SQL expression. > > I?ve minimised the example down to (hopefully) make it simpler. > > I have a table with an ISO date StartTime in it held as a string. I'd be inclined to store your dates as seconds since the epoch. That way arithmetic and comparisons become easy, and your SQL looks simpler. Convert to a string for display. But perhaps your application prevents that for some reason. -- Cheers -- Tim
[sqlite] Can't understand why I need this cast
On 2/18/16, Simon Davies wrote: > > Ok on sort order, but why > > SQLite version 3.8.11.1 2015-07-29 20:00:57 > Enter ".help" for usage hints. > Connected to a transient in-memory database. > Use ".open FILENAME" to reopen on a persistent database. > sqlite> > sqlite> select '11' <= 11; > 0 > sqlite> select '11' <= cast(11 as integer); > 1 > The documentation says: "An expression of the form "CAST(expr AS type)" has an affinity thta is the same as a column with a declared type of "type".". Since the RHS of the <= operator now has affinity, that affinity is applied to the LHS operand as well, resulting in both operands being treated as integers for the purpose of the comparison. -- D. Richard Hipp drh at sqlite.org
[sqlite] Can't understand why I need this cast
Hi,
I?m sure this is a really dumb question but I?m struggling to
understand why the following SQL is needed for what should be a trivial
SQL expression.
I?ve minimised the example down to (hopefully) make it simpler.
I have a table with an ISO date StartTime in it held as a string.
sqlite> .schema test1
CREATE TABLE "Test1" (
"Id" INTEGER NOT NULL,
"StartTime" TEXT NOT NULL,
PRIMARY KEY("Id")
);
I add in some test ISO date data that *looks* OK
sqlite> select * from test1;
1|2011-05-03T05:00:00Z
I run the following to check that the ISO date is between a range of
seconds, i.e. its greater than three hours ago and less than three hours
in the future. I know this is contrived but this is the minimum test
case from a far larger query.
select
strftime('%s' , starttime) ,
strftime('%s' , starttime) - (180 * 60) ,
strftime('%s' , starttime) + (180 * 60) ,
strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60))
,
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
,
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
from
test1;
What I expect to see is
1304398800|1304388000|1304409600|10800|10800|1|0
1. the ISO start time as secs - Correct
2. The ISO start time in secs minus 10800 - Correct
3, The ISO start time in secs plus 10800 - Correct
4. The value 10800 to check that the maths is correct - Correct
5. The value 10800 to check that the maths is correct - Correct
6. A check that the ISO value in secs is greater than the current time
minus 10800 - Correct
7. A check that the ISO value in secs is less than the current time plus
10800 secs - INCORRECT
if I CAST the value of the last column
select
strftime('%s' , starttime) ,
strftime('%s' , starttime) - (180 * 60) ,
strftime('%s' , starttime) + (180 * 60) ,
strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60))
,
(strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime)
,
strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60))
,
strftime('%s' , starttime) <= cast((strftime('%s' , starttime) + (180 *
60)) as integer)
from
test1;
I get the right answer
1304398800|1304388000|1304409600|10800|10800|1|1
I?m puzzled as to why this should be. Why would I need to cast an
integer to an integer. I??m sure there?s an obvious answer but I
can?t find it.
Any suggestions please?
Thanks,
Rob
[sqlite] Can't understand why I need this cast
On 2/18/16, Rob Willett wrote:
>
> select
> strftime('%s' , starttime),
> strftime('%s' , starttime) - (180 * 60),
> strftime('%s' , starttime) + (180 * 60),
> strftime('%s' , starttime) - (strftime('%s' , starttime) - (180 * 60)),
> (strftime('%s' , starttime) + (180 * 60)) - strftime('%s' , starttime),
> strftime('%s' , starttime) >= (strftime('%s' , starttime) - (180 * 60)),
> strftime('%s' , starttime) <= (strftime('%s' , starttime) + (180 * 60))
> from
> test1;
>
I don't exactly understand why you are doing the query above, but for
whatever your purpose is, the following formulation seems simpler to
me:
SELECT
strftime('%s', starttime),
strftime('%s', starttime, '-180 seconds'),
strftime('%s', starttime, '+180 seconds'),
(julianday(starttime) - julianday(starttime','-180 seconds'))*86400,
(julianday(starttime, '+180 seconds') - julianday(starttime))*86400,
julianday(starttime) >= julianday(starttime, '-180 seconds'),
julianday(starttime) <= julianday(starttime, '+180 seconds')
FROM
test1;
--
D. Richard Hipp
drh at sqlite.org
[sqlite] Can't understand why I need this cast
On 2/18/16, Simon Davies wrote: > sqlite> select '11' <= 11; > 0 > sqlite> select '11' <= cast(11 as integer); > 1 > sqlite> select '11' >= 11; > 1 Sort order in SQLite is: (1) NULLs, (2) Numeric values in numeric order, (3) Strings in the order of whatever collating sequence applies, and (4) BLOBs in lexicographical order. Hence, strings are always greater than numbers. Always. If you want to compare a string against it's numeric equivalent, you need to cast the string to a number. -- D. Richard Hipp drh at sqlite.org
