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
