Mailing List Archive: How change a ticket creation time?

How change a ticket creation time?

Mar 26, 2021, 11:05 AM

Post #1 of 3 (454 views)

Hi,

I have a project with about 200 tickets. They were all entered at about the
same time and represent things that have happened over the course of 2-3
years.

I want to be able to collect statistics about these tickets and, ideally,
want to use the ticket creation time from this moment onwards, because it
is automatically assigned to a ticket.

Unfortunately, those 200 tickets were not created 1 or 2 years ago because
we were not using trac for this purpose back then.

So, in order to fix things, I believe that I will have to modify some of
those 200 tickets by editing the underlying sqlite database (associated
with Trac 1.4.2).

What kind of gotcha's might I experience?

- Do I only need to change one value in one place for one ticket?
- For example, for ticket 77 I only need to change row X in table Y?

Thank you for your help with this!

Kindly,
Aikido Guy

--
You received this message because you are subscribed to the Google Groups "Trac Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to trac-users+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/trac-users/145ed1e0-37d7-4a4d-9b67-da8d83b6ab12n%40googlegroups.com.

Re: How change a ticket creation time? [ In reply to ]

rjollos at gmail

Mar 26, 2021, 1:45 PM

Post #2 of 3 (454 views)

Permalink

On Friday, March 26, 2021 at 11:05:02 AM UTC-7 aiki...@gmail.com wrote:

> Hi,
>
> I have a project with about 200 tickets. They were all entered at about
> the same time and represent things that have happened over the course of
> 2-3 years.
>
> I want to be able to collect statistics about these tickets and, ideally,
> want to use the ticket creation time from this moment onwards, because it
> is automatically assigned to a ticket.
>
> Unfortunately, those 200 tickets were not created 1 or 2 years ago because
> we were not using trac for this purpose back then.
>
> So, in order to fix things, I believe that I will have to modify some of
> those 200 tickets by editing the underlying sqlite database (associated
> with Trac 1.4.2).
>
> What kind of gotcha's might I experience?
>
> - Do I only need to change one value in one place for one ticket?
> - For example, for ticket 77 I only need to change row X in table Y?
>
> Thank you for your help with this!
>
> Kindly,
> Aikido Guy
>

Yeah, should be pretty straightforward. Make sure to backup your database
first. If you are using SQLite you can just copy $env/db/trac.db to backup
and to have a copy for experimenting.

See schema at:
https://trac.edgewall.org/wiki/TracDev/DatabaseSchema/TicketSystem#Tableticket

I'd write a Python script that takes a datestring and ticket id as input
args (changetime, tid)

env = open_environment(/path/to/env, use_cache=True)
dt = parse_date(changetime)
ts = to_utimestamp(dt)
env.db_transaction("""
UPDATE ticket SET changetime=%s WHERE id=%s
""", (changetime, tid))

open_environment:
https://www.edgewall.org/docs/branches-1.4-stable/html/api/trac_env.html#functions

parse_date:
https://trac.edgewall.org/browser/tags/trac-1.4.2/trac/util/datefmt.py#L611

to_utimestamp:
https://trac.edgewall.org/browser/tags/trac-1.4.2/trac/util/datefmt.py#L195
https://www.edgewall.org/docs/branches-1.4-stable/html/api/trac_util_datefmt.html#trac.util.datefmt.to_utimestamp

litecli is nice if you want to instead work directly with the database:
https://github.com/dbcli/litecli

Ryan

--
You received this message because you are subscribed to the Google Groups "Trac Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to trac-users+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/trac-users/6c6b69a4-ec59-4bff-8c12-90b84aa20fb3n%40googlegroups.com.

Re: How change a ticket creation time? [ In reply to ]

aikidoguy at gmail

Mar 27, 2021, 7:15 AM

Post #3 of 3 (454 views)

Permalink

On Friday, March 26, 2021 at 4:45:47 PM UTC-4 RjOllos wrote:

> On Friday, March 26, 2021 at 11:05:02 AM UTC-7 AikidoGuy wrote:
>
>> Hi,
>>
>> I have a project with about 200 tickets. They were all entered at about
>> the same time and represent things that have happened over the course of
>> 2-3 years.
>>
>> I want to be able to collect statistics about these tickets and, ideally,
>> want to use the ticket creation time from this moment onwards, because it
>> is automatically assigned to a ticket.
>>
>> Unfortunately, those 200 tickets were not created 1 or 2 years ago
>> because we were not using trac for this purpose back then.
>>
>> So, in order to fix things, I believe that I will have to modify some of
>> those 200 tickets by editing the underlying sqlite database (associated
>> with Trac 1.4.2).
>>
>> What kind of gotcha's might I experience?
>>
>> - Do I only need to change one value in one place for one ticket?
>> - For example, for ticket 77 I only need to change row X in table Y?
>>
>> Thank you for your help with this!
>>
>> Kindly,
>> Aikido Guy
>>
>
> Yeah, should be pretty straightforward. Make sure to backup your database
> first. If you are using SQLite you can just copy $env/db/trac.db to backup
> and to have a copy for experimenting.
>
> See schema at:
>
> https://trac.edgewall.org/wiki/TracDev/DatabaseSchema/TicketSystem#Tableticket
>
> I'd write a Python script that takes a datestring and ticket id as input
> args (changetime, tid)
>
> env = open_environment(/path/to/env, use_cache=True)
> dt = parse_date(changetime)
> ts = to_utimestamp(dt)
> env.db_transaction("""
> UPDATE ticket SET changetime=%s WHERE id=%s
> """, (changetime, tid))
>
> open_environment:
>
> https://www.edgewall.org/docs/branches-1.4-stable/html/api/trac_env.html#functions
>
> parse_date:
> https://trac.edgewall.org/browser/tags/trac-1.4.2/trac/util/datefmt.py#L611
>
> to_utimestamp:
> https://trac.edgewall.org/browser/tags/trac-1.4.2/trac/util/datefmt.py#L195
>
> https://www.edgewall.org/docs/branches-1.4-stable/html/api/trac_util_datefmt.html#trac.util.datefmt.to_utimestamp
>
> litecli is nice if you want to instead work directly with the database:
> https://github.com/dbcli/litecli
>
> Ryan
>
>
Thank you for the very detailed information! I will get this sorted in the
next days...

Kindly,
Aikido Guy

--
You received this message because you are subscribed to the Google Groups "Trac Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to trac-users+unsubscribe@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/trac-users/76ebcd3b-199a-42de-957f-06a30f2c2ebfn%40googlegroups.com.