Best way to sort TimeDifference in DataGridView

Question

I have the following problem;

My ListBased application is reading lots of stuff out of my dabatase. One of the read columns is called ´CreateDate´, which represents the datetime of the entrys creation. My List-Based application also calculates the TimeDifference from creation to now to show the user, how long this entry already exists. These informations are shown to the user in a DataGridView.

Right now the following code is used:

Dim a As DateTime = row.Item("CreateDate")
Dim min As Integer = DateDiff(DateInterval.Minute, a, Now) Mod 60
Dim h As Integer = DateDiff(DateInterval.Hour, a, Now) Mod 24
Dim d As Integer = DateDiff(DateInterval.Day, a, Now)
dgvRow.Cells(2).Value = d.ToString("00") & ":" & h.ToString("00") & ":" & min.ToString("00")

This code gives us the following Time-Difference Format: DD:HH:MM.

The problem with that: If users use the Sort function of the DataGridViews "Wait Time"-Column it sorts this column as String which of course isnt correct.

One Possible Solution is for example showing the TimeDif as Minutes only, but the longer it gets the worse it becomes for the user.

Another possible solution: Just use the DateTime itself instead - This one of course would work, but the users do not want to calculate the TimeDif themselves.

What can i do, to make the Wait Time Column Sortable?

Example:

Answer 1

The "best way" to sort or work with any data is in its native form, not the string representation of it which will require tearing them apart and converting or parsing them to something which will sort properly.

In the case of a span of time, a TimeSpan or even integers will work. The DGV is quite capable of sorting either correctly.

The OP doesnt say, but I assume that the DGV is bound to a table rather than copying data from the DB to the DGV; if not it should be: it is always better to work with the data than the user display. There are a couple of ways to do this. The first solution will use a TimeSpan column since that is what it is, the second will use an integer column.

TimeSpan

After you fill the DataTable, add a TimeSpan column to it and fill that column:

Dim dc = New DataColumn("Elapsed", GetType(TimeSpan))
dtSample.Columns.Add(dc)

Dim dtN = DateTime.Now
For Each r As DataRow In dtSample.Rows
    r("Elapsed") = dtN.Subtract(r.Field(Of DateTime)("StartDate"))
Next

By and large that is all there is to it. The DGV will know how to properly sort the column. The rest is purely to handle the display format (the default, 6.01:32:26.7898087 is decidedly unfriendly). Unfortunately formatting using CellStyle doesn't work for a TimeSpan so you need to use the CellFormatting (or use a CustomFormatter which is a good technique to know).

Private Sub dgv2_CellFormatting(sender As Object,... etc
    If e.ColumnIndex = 3 Then
        Dim ts As TimeSpan?      ' Nullable so we can detect fails
        If TypeOf (e.Value) Is TimeSpan Then
            ts = CType(e.Value, TimeSpan)
        End If

        If ts IsNot Nothing Then
            e.Value = String.Format("{0}:{1:00}:{2:00}", ts.Value.Days,
                           ts.Value.Hours, ts.Value.Minutes)
        Else
            e.Value = ""
        End If
        e.FormattingApplied = True
    End If
End Sub

Results:

The image shows the before and after sorting. Notice that the single digit days sort lower than the 10s automatically.

Integer

This is perhaps a litter simpler: rather than adding a column to the DataTable, let SQL calculate the difference to some base unit such as seconds:

Dim SQL = "SELECT Id..., DateDiff('s',[StartDate], Now()) AS Elapsed FROM..." 

The SQL syntax may vary depending on your provider, but the result is that teh query will both calculate the difference and add an integer "Elapsed" column to the resulting DataTable. This elides loop to manually add the value. And, of course the DGV knows how to sort integers. Next change the CellFormatting event to convert to a TimeSpan for display:

    If e.ColumnIndex = 3 Then
        Dim ts As TimeSpan?      ' Nullable so we can detect fails
        If TypeOf (e.Value) Is Int32 Then
            ts = TimeSpan.FromSeconds(CType(e.Value, Int32))
        End If

        If ts IsNot Nothing Then
            e.Value = String.Format("{0}:{1:00}:{2:00}", ts.Value.Days, ts.Value.Hours, ts.Value.Minutes)
        Else
            e.Value = ""
        End If
        e.FormattingApplied = True
    End If

in both cases, you are just changing how the data is displayed to the user. Because of this - and because "Elapsed" is a calculated value - that column cant be edited by users. With AutoGenerateColumns = True, your DGV should make that column ReadOnly.

Answer 2

I've got it working myself using the following code as Eventhandler:

Private Sub DataGridView1_SortCompare( _
    ByVal sender As Object, ByVal e As DataGridViewSortCompareEventArgs)
    If DirectCast(sender, DataGridView).SortedColumn.Equals(DirectCast(sender, DataGridView).Columns(2)) Then
        e.SortResult = CInt(e.CellValue1.ToString.Replace(":", "")) - CInt(e.CellValue2.ToString.Replace(":", ""))
        e.Handled = True
    End If
End Sub

Since I just got it working it might still contains bugs so be aware of that before using.

Answer 3

You can simplify your code somewhat by just subtracting the date from Now and then using String.Format to get the desired out:

Dim a As DateTime = row.Item("CreateDate")
Dim waitTime = DateTime.Now - a
dgvRow.Cells(2).Value = String.Format("{0:000}:{1:00}:{2:00}", waitTime.TotalDays, waitTime.Hours, waitTime.Minutes)

And as long as you use a fixed length for the days (that is the same length or larger than the longest number in your data) and two digits for the hours mins, when these are sorted as a string they should be in chronological order