[sqlite] Can't understand why I need this cast

Quan Yong Zhai Thu, 18 Feb 2016 19:01:04 +0800

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

Reply via email to