Hi Mark,
On Wed, Jan 4, 2012 at 9:59 AM, Cooke, Mark <mark.co...@siemens.com> wrote:
> > 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.
That's correct. For Postgres something like this should work:
SELECT ticket, TIMESTAMP WITH TIME ZONE 'epoch' + time/1000000 * INTERVAL
'1 second' AS created,
author, field, oldvalue, newvalue from ticket_change
WHERE author = $USER AND
(TIMESTAMP WITH TIME ZONE 'epoch' + time/1000000 * INTERVAL '1 second' >
now()-interval '7 days')
ORDER BY created DESC;
There might be a more elegant way to do it but that seems to do the job for
me at least.
-Ethan
--
You received this message because you are subscribed to the Google Groups "Trac
Users" group.
To post to this group, send email to trac-users@googlegroups.com.
To unsubscribe from this group, send email to
trac-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/trac-users?hl=en.
