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

[Richard Hipp]

On 2/18/16, Rob Willett <rob.sqlite at robertwillett.com> 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