Trac - WHERE statement in SQL lite query is "false" for all tuples altough it should not be

Question

The following SQL Lite statement in Trac

  SELECT 
  id,
  changetime / 1000000 as 'MODIFIED',
  strftime('%s',"now") as 'NOW',
  ((strftime('%s',"now")) - (changetime / 1000000)) as 'DIFF'
  FROM ticket
  WHERE (CAST(((strftime('%s',"now")) - (changetime / 1000000)) AS NUMERIC) < 86400.0)

results the following output:

"changetime" is a field from Trac (type: int64). What I want to achieve is that all tickets which changetime is within one week are selected. I had heavy problems because changetime is int64 and strftime returns something different. This is why I am dividing by 1000000.

Now I have the problem that the WHERE statement is always false altough as you can see on the screenshot I have tickets where it applies (made screenshot after removing WHERE clause temporarly).

Does anybody know what is going wrong?

Answer 1

SELECT
  id,
  changetime as modified,
  strftime('%s',"now") as date,
  (strftime('%s',"now") - changetime / 1000000) as 'DIFF'
FROM ticket
WHERE (CAST(strftime('%s',"now") AS NUMERIC) - changetime / 1000000) < 86400.0

works (tested on Trac 0.11 with divider 1 instead of 1.000.000), because you have to do the type conversion before going on with calculation - can't calculate with the date strings from strftime right-away.

Note, that some special column names ('created', 'modified', 'date', 'time') trigger automagic formatting as date and/or time. TicketQuery works nicer in this respect, as you can specify time limits more natural as changetime=1w..now, but you're asking implicitely for a solution in the TracReports domain.