Dealing with date/time fields and indices

In the OP's management program, there is a log of forms that are opened that is intended for me to see which forms are opened the most, so that I can improve them - and not improve those forms that are never opened. During a given month, there can be many entries for the same form per user: at the end of every month these entries are collapsed into one tuple per user, where the number of entries is stored in one of the fields. These entries obviously contain no data as to what use the forms are put (e.g. docket number, customer number, etc).

The OP wanted to maintain a log of sub-forms that were opened per docket (never mind if that isn't clear): there are two main differences between this and the currently existing program log. Apart from the minor fact that the program log is written to whenever any form is opened (here we're talking about five or six forms only), the docket number has to be saved, and as the primary key of the table is docket/user/form/time, the time and date of opening has to be saved, as opposed to only saving the date.

Whilst Firebird has a DATE datatype, this seems to be limited to dates (i.e. 13/10/25) only and does not include times. Not only that, it seems impractical to create an index on a date, as the field has to be defined as NOT NULL for there to be an index and not all date fields have a value at all times. I see that in the past I've used a NUMERIC (15, 6) datatype to store a date and time - Delphi uses the TDateTime type for this that can be seen as a float, e.g. 45942.53454 (that's 12/10/25 at approximately 12:20 pm). There is no problem in defining an index for this type, or indeed creating the primary key of the table with the date stored as a float.

I opened a few subforms in order that there should be some entries in the table, then started work on displaying the data. Obviously I don't want the date/time field to be displayed as 45942.53454, but rather 12/10/25 12:20; this can easily be done by means of a calculated field.

Now the fun starts: when a query is defined in a form for a report, indices (or as Delphi would inelegantly call them, indexes) are opened for each column as shown here¹. This is done in a common procedure. If I define that date/time field to be an internal calculated field ², then two indexes (one ascending, one descending) will be defined for this field - but they're indexing the string representation of the date, not the date itself. Not only that, it seems that the column is sorted by the time, not the date (and anyway a naive sort would have 10/11/2025 before 20/10/2015, which is wrong). I wanted what is column 2 to be sorted by column 5.

At first I wrote some convoluted code in the OnTitleClick handler that changes the index by which the query is displayed when the user clicks on the title of a column in the grid, but I thought that there must be a simpler way. I redefined the date/time calculated field as an ordinary calculated field; this meant that my standard code would define indexes named idx0, idx1 for the first column, idx2 and idx3 for the second column, and idx6..idx11 for the other fields, but no idx4 and idx5 for the date/time column. My intention was to add the two missing indexes after the common procedure had built the first eight indexes. This didn't work.

When I examined the common code, I saw why: after the indexes are built, the following three lines do some form of magic (or make up for a bug in Delphi 7):

     alist:= tstringlist.create;
     GetIndexNames (alist);
     alist.free;
  

As my new friend CoPilot explains, calling ClientDataSet.AddIndex(...) adds the index definition to memory, but it doesn't immediately update the internal index list. When you call those three lines, they forces the dataset to refresh its internal index list, effectively committing the index definitions. It's a side effect of accessing the index metadata.

So when I added manually two more indexes in the unit code, these weren't being added. Not only that, if I call AddIndex again later and then call GetIndexNames, the dataset refreshes its index list but only with the currently defined indexes. If the earlier ones weren't persisted (e.g., saved to a file or re-added), they get lost. There is another possibility, of calling IndexDefs.Update that forces the dataset to recognize all index definitions without needing a dummy TStringList. This didn't work for me.

Eventually I realised that the way to solve the problem was not to call the common procedure for creating indexes, but to include it directly in my unit code along with the creation of the two extra indexes and only then to call GetIndexNames. This works perfectly, so when the user clicks on the title of the date/time column, the index that is based on the original date field (not the string field) is called into play, and the rows sort properly according to date.

I didn't expect this level of complexity when I originally defined the DocketLog table.

Internal links
[1] 475
[2] 1673

---

This day in blog history:

Blog #	Date	Title	Tags
140	14/10/2008	Where have I been all these months?	Literature, Peter Robinson, David Lodge
415	14/10/2011	Dieting has a number of destructive side effects	Martin Seligman, Diet, Acupuncture
516	14/10/2012	Friday is cooking day	Cooking
765	14/10/2014	Some days you're the pigeon and some days you're the statue	DBA, Films
1083	14/10/2017	The seven stage model for developing enhancements	DBA
1266	14/10/2019	Priority: LIKE cannot accept a variable	Priority tips
1430	14/10/2021	Potassium levels in food	Health, Food science, CPAP, Blood pressure
1536	14/10/2022	Gillian McPherson	1971
1838	14/10/2024	Terminating threads	Programming, Delphi, Threads