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
