Time Trial Query Help Page

Below you see the form for the Time Trial Query (ttq.pl) facility. We will describe how you can: Examples will be given and at any time you may also perform your own experiments by entering information into the form and hitting one of the buttons to submit your query. I hope that you enjoy using this program. It was written by a keen time trialist for keen time trialists. Please let me know if there's anything which I can do to improve your usage of this software - either by email (found at the bottom of this help file) or in person at one of the time trials (I'm at virtually all of them!).

Searching for Patterns Within The Data Fields: There are text boxes for each data field and you may type in the patterns which you wish to match in any of those particular fields. There is also a big text box at the top in which you can put patterns that involve multiple fields. Note that the search will be an AND'ing of the blank delimited strings in the text box. A "!" at the start of a (blank delimited) pattern indicates that you want results which do NOT match the pattern following the "!". There is a checkbox which allows you to specify whether the search should be case-sensitive or not. By default, all searches of the individual text fields are case-insensitive. Each of the blank delimited search patterns may be a postfix regular expression. Don't worry about this, most of the time you'll just be typing words to search for. I have included a few more complicated examples in the table below.

Complexity Level of Query Form - Simple or Advanced: Obviously the most important measurable in a time trial is the time, but we keep track of much more, basically because of our desire to award and motivate people for various types of noteworthy performances.

Format of Results Displayed - Tabular or Graphical:

Sorting the Results: You may sort the results with repect to any particular field by hitting the button with that field name. The column upon which the results are sorted will be clearly visible because of the white background which is applied to it. Note that every effort has been made to do provide meaningful sorts. For instance, when ordering on the category rankings, the ordering will rank the fastest person in the biggest category as first, the fastest person in the 2nd biggest category next, ..., the fastest person in the smallest category next, the 2nd fastest in the biggest category next, the 2nd fastest in the 2nd biggest category next, and so on. As well, sensible default subsortings are defined, though you may choose different different subsortings if you desire. Please let me know if you disagree with any of the default sub-sorting decisions made. Where it's interesting to do so (for instance a sort by Category), if you have not clicked the Suppress Subgroup Averages checkbox, you will see the averages of the subgrouped category data.

SubSorting the Results: As mentioned above, sensible default subsortings are defined for each of the sort buttons. Still, you may define your own subsorting orders by filling in the appropriate fields in the text-box following "SubSortFields:" at the top of the form.

Case-sensitive Search Across All Fields: As well you may do a case-sensitive search for patterns spanning all fields. The order of the fields is the same as that in the output table (if you show all fields). Note that decisions in 2003 to only allow the OBC BAR rankings to include the OTT 15km, and in particular not the ATT 15km, require the use of this field. This is so since we need to allow the ATT series for the 40km results, but want to exclude the ATT (and the WTT) series for the 15km results. This search was set to be case sensitive to lessen the possibility of bogus matches - for instance, it is helpful to not pull in parts of surnames (such as Potter) when we're trying to match on the series name OTT, and so on.

Margin Markings Showing Personal/Category/Gender Bests: In the left hand margin of the result pages, we flag any instances of personal/category/gender bests with coloured boxes as indicated below:

A, S, Y or s in a white box marks database All-time, Series all-time, Year or series/year gender/distance bests.A, S, Y or s in a non-white box marks database All-time, Series all-time, Year or series/year gender/distance bests for the category associated with that colour.   Database PB for distance,   Year PB for distance,   Year-Series PB for distance.

Filtering for Just the "Best" Results: There are a number of options which may help you limit your search - for instance, to just the Gender Bests or Series Bests for the Entrants which match your specified search strings. You can select from the radio buttons in the second line of the form. Note that the bests are calculated for each distance in the results which your search strings would find.

Subgrouping Data: Aside from the above ability to sort or subgroup on any of the fields, there is also the ability to subgroup an individual's best performance of any specified number of events of any number of the different TT distances. You need only specify the number of best performances you want of each distance. You can show averages of this subgrouped data by clicking the "Show Subgroup Averages" checkbox. Right now you must have the "All" radio button checked. When I get some time, I'll expand this functionality to the other filter options.

Sorting the Subgroups of Results: There are a number of ways in which you might like to sort the subgroups of results selected above via the "Show best:" line. For instance, the default sorting for the subgroups is by AvgSpd - i.e. the Total Distance for all of the rides in the subgroup divided by the Total Time. You can also order the subgroups by AvgAvgSpd (i.e. the average of the average speeds), as we have done for the OBC BAR awards since 2003. Sorting the Subgroups by the StdDev of the average speed will show the most consistent subgroups first. As well, their are subsortings relative to various PerformanceRatios.

Showing Weather Information: Weather information, if known, is automatically inserted for all queries which pull results from a single time trial event - ie., if the date and series are fully specified. In addition, weather information can be shown for other queries if the results are sorted by date and the "Show SubDate Info" checkbox is checked.

Event Factor: The Event Factor indicates how "good" a day is, by which I mean how close entrants are to their year-series personal bests in this particular event. I calculate the Event Factor by looking at all riders who have ridden at least 3 TT's in the current season in the series of interest at the distance of interest, and for these riders, calculating the average of the ratio of the cubes of the rider's speed in this event to his/her best speed at this distance in this series this year. An attempt is made to ignore results for which riders have run into mechanical difficulties, etc. (as indicated in the comment field of their result record). Note that such a measure will reflect both weather and fitness (which tend to improve as the season progresses) states. Note that the maximum Event Factor possible is 1 and this occurs only if all of the riders (who have ridden at least 3 times in the current year in the particular series at a particular distance) ride their fastest time on this day. Note, of course, that Event Factors can only be calculated after at least three time trials are held in a particular series at a particular distance. Once this number of time trials occur, Event Factors are shown for all of the earlier time trials in the series (at least if there are riders in that day who have done at least 3 time trials). Obviously one shouldn't take these measures too seriously since there is a tendency for the winds to drop in the late evening, ie., conditions may tend to be quite different for the early starters than they are for the late starters, and as well, riders who start time trialling later in the season may not be fit enough to perform as well as the more experienced riders do part way through the season. Still, as defined above, the Event Factor gives some information as to the average difficulty of the night. Look at the following example and do some other related queries to see how well it works. One would expect that the speeds decrease monotonically with the decreasing EventFactors. Note that, as described in the form, the rider's best time in the season at that distance will be marked with colour in the margin (gold if the rider achieves a database best at that distance, silver for a year's best at that distance or bronze(orangish) for a year-series best at that distance. Hence, we would expect that many riders would have a colouring in the margin for the day with the highest event factor (ie., the first of their results when they are sorted by the EvtFactor).

Performance Ratios: Power output is said to be proportional to the cube of the speed, and hence we calculate some ratios of cubes of speeds to measure performance. There is a select list which allows you to compare the cube of the speed to various other cubes of speed. An explanation of the entries in the list is given in the table which is included below. As well, there is an EF Checkbox which divides the Performance Ratios by the Event Factor Ratio, ie., the ratio of the Event Factors for the two events involved in the Performance Ratio. This is done as a reasonable attempt for comparing performances on different days. Note that in the case where more than one result exists with the speed used in the denomimator of the performance ratio, the smallest Event Factor of those results is used (since this would correspond to the "better" result - ie., same time under on a more difficult day).
ComparisonDescription
PB Yr-Ser PreCube of speed divided by cube of the best speed the person has done previously in the current year in the current series for the current distance
PB Yr-SerCube of speed divided by cube of the best speed the person has done in the current year (before or after) in the current series for the current distance
PB YrCube of speed divided by cube of the best speed the person has done in the current year (before or after) series for the current distance
PB DbCube of speed divided by cube of the best speed stored in the database for the person series for the current distance
CB Yr-SerCube of speed divided by cube of the category best speed done in the current year (before or after) in the current series for the current distance
CB YrCube of speed divided by cube of the category best speed done in the current year (before or after) for the current distance
CB DbCube of speed divided by cube of the category best speed stored in the database for the current distance
CB ShownCube of speed divided by cube of the category best speed shown in the current results for the current distance
GB Yr-SerCube of speed divided by cube of the gender best speed done in the current year (before or after) in the current series for the current distance
GB YrCube of speed divided by cube of the gender best speed done in the current year (before or after) for the current distance
GB DbCube of speed divided by cube of the gender best speed stored in the database for the current distance
PB Spd ShownCube of speed divided by cube of the person's best speed shown in the current results for the current distance
CB Spd ShownCube of speed divided by cube of the same-category best speed shown in the current results for the current distance
GB Spd ShownCube of speed divided by cube of the gender best speed shown in the current results for the current distance
B Spd ShownCube of speed divided by cube of the best speed shown in the current results for the current distance

Handicapping: This site contains a number of handicapping algorithms, and you may apply any of them to any set of query results.

More Search Examples:

Search Examples: Most times, you will be able to find what you want with no difficulty. Please don't let the examples or discussion in the following table scare you off! Note that if you wish to search for a non-alphanumeric character which (also) happens to be a "special" character for Perl (eg., brackets), you will have to precede that character by a backslash. The last example in the table shows this.

Search For
TextFieldPattern
Other SettingsExplanation
Entries containing "Paul" in "1999"
Name Paul
Date 1999
Check the "Case-Sensitive" checkbox if you care about the capitalization. You will match "Paula" as well as just "Paul". To avoid matching Paula, you could use a Perl regular expression (regexp) to specify that "Paul" must end the firstname - ie., by replacing the text entry "Paul" with "Paul$".
Entries other than Team Time Trial or Tandem ones for "Moerman"
Name Moerman
Category !TTT !TAN
Check the "Case-Sensitive" checkbox if you care about the capitalization. Here you will match all of the Moerman's individual (ie., not team and not tandem) results.
Entries for Allison or Andrea McKay in 2000-2002.
Name allison|andrea mckay
Date 200[0-2]
Uncheck the "Case-Sensitive" checkbox. Again, if you want to, you could require proper capitalization and require that the strings start and end on word boundaries, but normally you just won't care.
Mary Ajersch's personal season's bests in the WTT series.
Name Mary Ajersch
Series WTT
The "Entrant Series Bests" checkbox must be checked. You could type less of Mary's name and still get the same results. You could also type everything in lower case if the "Case-Sensitive" checkbox is not checked, and in this case you would not have received extraneous matches.
All Tandem entrants in the Jul 5 2001 time trial.
Date 2002-Jul-05
Category TAN
   
Find all individual female entrants who have ridden a 15km time trial in between 20:00 and 20:59.
Distance 15km
Time 20:
Category [A-Z]{2}F
  Here, for the category, we're looking for a three character alphabetic string ending in "F" (which would correspond to the female category abbreviations - eg., VAF, SEF and so on). We could also just have looked for any string ending in "F" (ie., "F$"), or an "F" preceeded by 2 other characters (ie., "..F"), and we could have done a case-insensitive search.
Look for all entries which contain parentheses or quotes (since these occur around team names or excuse fields!).
Top Text Box [("]
  We could also have written the pattern as \\(|". The two backslashes preceding the opening parenthesis are needed to escape the special meaning which the open parenthesis would otherwise have as a perl and mysql regular expression. If you did not include these two backslashes here, you would receive an error from the perl DBI interface.

Please send your comments/suggestions to Celia McInnis