> On Thu, Dec 29, 2011 at 11:13 AM, 3vix6 <[email protected]> wrote: > > Hi, > > Here's what I'm trying to do.. I need to have a ticket > report that > will bring up all user's activity to tickets within the > last 7 days. > > Here is what I have so far.. > > > SELECT ticket, time as created, time, author, field, oldvalue, > newvalue from ticket_change WHERE author = $USER and > julianday(time, > 'unixepoch') > julianday('now')-7 > > > ORDER BY time DESC > > For some reason, this isn't working.. Is there anyone > out there can > see what I'm doing wrong?
> -----Original Message----- > From: [email protected] > [mailto:[email protected]] On Behalf Of Ethan Jucovy > Sent: 29 December 2011 16:36 > To: [email protected] > Subject: Re: [Trac] needing some help with sqlite Ticket Query > > Trac stores timestamps as microseconds, so you need to > convert it in your query. The following works: > > SELECT ticket, time as created, time, author, field, oldvalue, > newvalue from ticket_change > WHERE author = $USER > AND julianday(time / 1000000, 'unixepoch') > julianday('now')-7 > ORDER BY time DESC ...this does not work for me. Digging seems to indicate that julianday() is an SQLite function and is not valid for my PostgreSQL backend. ~ mark c -- You received this message because you are subscribed to the Google Groups "Trac Users" group. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/trac-users?hl=en.
