Trac sqlite date query

Question

I am having a hell of a time generating a report with dates of creation for my tickets.

here is my sqlite query( I am directly querying the db)

.header on
.mode csv
.output opentickets.csv
SELECT DISTINCT
   id AS ticket, summary, status, priority, t.type AS type, 
   owner, time as created

  FROM ticket t, ticket_custom q
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE t.id = q.ticket AND status <> 'closed'
 ORDER BY  priority, time;

But the value I get for the time of creation is garbage: I get 1341324096360000 when I should get 2012-07-03

I have tried unix epoch time conversion... query:

.header on
.mode csv
.output opentickets.csv
SELECT DISTINCT
   id AS ticket, summary, status, priority, t.type AS type, 
   owner, datetime(time, 'unixepoch') as created

  FROM ticket t, ticket_custom q
  LEFT JOIN enum p ON p.name = t.priority AND p.type = 'priority'
  WHERE t.id = q.ticket AND status <> 'closed'
 ORDER BY  priority, time;

I get: -1413-03-01 13:07:12

What am I missing?

Answer 1

CL is correct that the time is stored in microseconds. You can see here in the schema that the field for time is an int64 and in the release notes for .12 the say that they have changed the time from "seconds since epoch" to "microseconds since epoch".

As for my experience with displaying time in Trac queries, I did not have to do any conversion. Here is a query that I use to display all changed tickets for the last 7 days. As you can see, I show the ticket change time as part of the select and Trac formats it correctly. Trac will auto format created, modified, date, time field as a date and/or time. See this Trac wiki page for more details.

    SELECT 
tc.Ticket AS ticket, 
tc.Time as time, 
c.value as  __group__,
ifnull(tc.Newvalue,0) as total,
tc.Author  AS author,         
tc.Field as field     
from ticket_change tc 
LEFT JOIN ticket t on t.id = tc.ticket
LEFT JOIN ticket_custom c on c.ticket = tc.ticket and c.name= 'customer'
LEFT JOIN ticket_custom tt on tt.ticket = tc.ticket and tt.name = 'totalhours'
where field = 'hours' and c.Value is not null
and tc.Time > strftime('%s',CURRENT_DATE-7) +0
and tc.Time < strftime('%s',CURRENT_DATE) +0
and tc.Author = $USER
and tt.value is not null

If you are looking to do some special fomatting, look at the SQLite docs on time but here is a quick example

strftime('%m/%d/%Y', time / 1000000, 'unixepoch', 'localtime') 

I hope this helps.

Answer 2

Unix timestamps are in seconds, but it appears these values are microseconds:

> SELECT datetime(1341324096360000 / 1000000, 'unixepoch');
2012-07-03 14:01:36