How to add date range to this SQL in trac?

We have the following query working as expected and have one last thing left in our modification of trac:

SELECT
   p.value AS __color__,
   owner AS __group__,
   id AS ticket,
   severity,
   status,
   priority,
   summary,
   component, 
   milestone, 
   t.type AS type, 
   time AS created, 
   description AS _description, 
   reporter AS _reporter, 
   changetime AS modified, 
   time AS _time,
   reporter AS _reporter 
FROM 
   ticket t 
   LEFT JOIN enum p 
   ON p.name = t.priority 
      AND p.type = 'priority' 
WHERE 
   status = 'closed' 
ORDER BY 
   owner, 
   p.val开发者_开发技巧ue, 
   t.type, 
   time

We need to date range to this to only pull out tickets closed between Aug 5 and Aug 17. We need to be able to edit the query to change the date range as needed as we need a few different date range. Can you please advise how to change the query to have date range between Aug 5 to Aug 17 ?

Add to the end of your WHERE clause:

AND changetime BETWEEN '2011-08-05' AND '2011-08-17'

I'm assuming 'changetime' is the field that stores the date the ticket was closed.

Do you really want to choose t.changetime as a constraint? Any edit or comment after closing the ticket would mess with your report. Anyway, for now let's assume you can rule this out somehow.

As hinted in my comment to Austin's answer, I can't confirm nor did I ever expect that a date string would work here. t.changetime holds Integers (Trac 0.11) or INT/LONG values (Trac 0.12) so must be compared against such values.

I'll show you in short how to produce them here:

$> date -d '2011-08-06' +%s
1312581600
$> date -d '2011-08-18' +%s
1313618400

As you see the conversion from string to POSIX seconds after 1970-01-01 is quite easy on any terminal window running a shell like BASH. For Trac 0.12 you'll need microseconds format, so just add 6 zeros or use

echo $(($(date -d '2011-08-18' +%s) * 1000000))
1313618400000000

Note, to include full 2011-08-17 you'll want to go past it, to the beginning of the following day, hence '2011-08-18' might be what you want to convert.

Now put it all together and you'll get

AND t.changetime BETWEEN 1312581600 AND 1313618400

or for Trac >= 0.12

AND t.changetime BETWEEN 1312581600000000 AND 1313618400000000