On Monday, June 1, 2020 at 2:22:12 AM UTC-4, RjOllos wrote: > On Sunday, May 31, 2020 at 7:07:02 PM UTC-7, Aikido Guy wrote: >> >> On Sunday, May 31, 2020 at 10:05:30 PM UTC-4, Aikido Guy wrote: >>> >>> A TicketQuery (working before upgrade to Trac 1.4.1) has triggered the >>> following error message in Trac 1.4.1 (the log file contains): >>> >>> ERROR: Macro TicketQuery(...) >>> >>> ValueError: invalid literal for int() with base 10: '2018/07/03' >>> >>> Notes: >>> (1) I am ordering by a column that use to be of type "text" and was then >>> formatted by a plugin (if I remember correctly... it has been about 6 years) >>> (2) The new column is now of type "time" with format "date" >>> >>> Questions: >>> (1) Do I need to change the backing sqlite database for this column type >>> change from "text" to "time"? >>> >>> Forgot to mention that I also saw #12325 that may be related... >> >> You were probably using DateFieldPlugin, and encountering the problem > described in this ticket: > https://trac-hacks.org/ticket/11856 >
Yes indeed! That is the plugin's name ;) So, yes, like you've already hinted at, if you want to use a > TicketCustomField of type time, you'll have to migrate your data. Or if > you've created a special column for time data, either directly or via a > plugin. In the latter case, you'll need to change the column type from text > to int64 (and type defined in Trac, which maps to appropriate types in each > supported DB). > Understood... If you are using a custom field of type time, then no need to worry about > the database type because all data in the ticket_custom table gets stored > as text. > Thanks for that schema reminder! Very helpful tip ;) Example: > > [ticket-custom] > time1 = time > time1.format = date > > Created a ticket #155 with the "time1" field having value "May 1, 2020": > > $ litecli ../tracenvs/proj-1.3/db/trac.db > Version: 1.2.0 > Mail: https://groups.google.com/forum/#!forum/litecli-users > GitHub: https://github.com/dbcli/litecli > ../tracenvs/proj-1.3/db/trac.db> SELECT * FROM ticket_custom WHERE > ticket=155 AND name='time1' > +--------+-------+--------------------+ > | ticket | name | value | > +--------+-------+--------------------+ > | 155 | time1 | 001588316400000000 | > +--------+-------+--------------------+ > 1 row in set > Time: 0.010s > > > I did some quick investigation on conversion: > > >>> from trac.util.datefmt import parse_date, to_utimestamp > >>> d = parse_date('2018/07/03'.replace('/', '-')) > >>> d > datetime.datetime(2018, 7, 3, 0, 0, tzinfo=<LocalTimezone "PDT" -1 day, > 17:00:00>) > >>> t = to_utimestamp(d) > >>> t > 1530601200000000 > > You can convert "t" to a string and insert into the ticket_custom table. > > You should be okay if your server is in approximately the same timezone as > your users, but if not, and if you care, you need to determine who inserted > the data and what timezone they were in. That sounds painful. > > - Ryan > All very excellent points. And it would be very painful if I needed hourly information! That's for sure! My current global trac.ini contains something similar to your example: [ticket-custom] time1 = time time1.format = date time1.label = "Blah (YYYY/MM/DD) time1.value = time1.order = 5 I've followed all of your advice and am now left with one small (not urgent) question. In particular, my original TicketQuery now works (yah!); however, instead of having '2018/07/03' shown in my table, I have "*at* 2018/07/03". - Is there a configuration option to control how the time is output? In my case, since there are no hours, minutes or seconds, it could be "*on *2018/07/03" instead? - Or maybe there is some kind of option to control a little more the format of the date being output? i.e. a way to remove the "at"? or to add an "on" etc... As a side thought as I am typing... is the "*at* 2018/07/03" supported across languages? 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+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/trac-users/31f21a1d-85c1-43f3-a514-d103ccdaf341%40googlegroups.com.
