1

am trying to export alle the change history of all my trac tickets via a tab delimited text file export. I successed so far with the following SQL Statement: 

SELECT 

id AS Ticket,
tc.author as author,
tc.field as field, 
tc.newvalue as comments_new
from ticket t
LEFT JOIN ticket_change tc ON ( t.id=tc.ticket )

But I need also the time and date of each entry. There is a field time in the table ticket_change but it is only the time without the date. Seems like I have to use some functions within the SQL to get what I need. Can anybody help me to get there?

Every Help is very appreciated. Best Regards Ali

Ali Hamoudi
  • 11
  • 1
  • An example im Mysql how to display the time differents between datetime now and the one in the change_table would also help to solve this problem. – Ali Hamoudi Jun 02 '16 at 09:28

1 Answers1

1

The time field is an integer representing epoch microseconds. You can use the Trac utility trac.util.datefmt.from_utimestamp to convert the value to datetime format. That only helps if you are working in Python though. In other languages, you can use recipes for converting from unix seconds. Note you must divide the value in Trac by 1e6 first, to convert from microseconds to seconds.

RjOllos
  • 2,900
  • 1
  • 19
  • 29
  • Hi @RjOllos thank you for your answer. Currently I have only access to MySQL so the Pyhton way is not possible.I tried following statement but it is not working because of syntaxs 
      'SELECT 

id AS Ticket,
tc.author as author,
tc.field as field,
(select(FROM_UNIXTIME(tc.time, '%d.%m.%Y %H:%i')))  as time,  
tc.newvalue as comments_new
from ticket t
LEFT JOIN ticket_change tc ON ( t.id=tc.ticket )   
    '
     – Ali Hamoudi Jun 02 '16 at 09:23
  • I get the following when I try to use FROM_UNIXTIME Report execution failed: no such function: FROM_UNIXTIME – Ali Hamoudi Jun 02 '16 at 09:30
  • You'll have to check your MySQL version and then consult the documentation to see if that function is available. – RjOllos Jun 02 '16 at 09:34
  • THANK YOU for reporting what I couldn't figure out: that the times are stored in _microseconds_, so you have to divide them by 1e6. None of the examples I found showed that, and without it `from_unixtime(time)` always returns NULL. – Andrew Schulman Jan 05 '18 at 14:51