0

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:

enter image description here

"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?

Florian
  • 388
  • 2
  • 3
  • 13
  • You should explicitly mention you Trac version, because this time field format changed for Trac 0.12, so you have a 0.12.x version. Furthermore it's not apparently clear, why you can't use the TicketQuery module, that has better time range selection support. – hasienda Jul 03 '12 at 17:34

2 Answers2

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.

hasienda
  • 2,390
  • 1
  • 13
  • 16
0

I have a report with within Trac that gets me tickets that have changed in the last 7 days and I am handling the where clause with the following SQL.

from ticket_change tc 
where tc.Time > strftime('%s',CURRENT_DATE-7) +0
and tc.Time < strftime('%s',CURRENT_DATE) +0
Josh Laase
  • 326
  • 1
  • 13