Link to home
Create AccountLog in
Avatar of geoffsweb

asked on

Need to handle time and compare times (not am/pm)

I am using ASP.NET 2.0 and C#
I need to allow users to enter in their running time, like 3:53:01 (3 hrs, 53 minutes, 1 second).  Then store this in an Access database.  Then I need to gather the times and compare them, as to make a Top 10 list of running times.  Do I take the inputted times and store them in a text field or date/time field (Access DB).  Then after I retrieve the times back, how do I compare them?  So if I have 3 times, 2:45:07, 2:50:55, and 2:15:45 ... I need to be able to compare them and sort them by fastest time.
Thanks in advance for the help/suggestions.
Avatar of ZachSmith
Flag of United States of America image

You should be able to use the DATEDIFF function for this:

If you want to calculate the difference in C#, use the TimeSpan object.
Why don't you retrieve them from the database in the sorted order?

I would store it as DateTime.  It will store the AM/PM also but that should not effect the ranking.  Even if you had a time of 12:30:35 PM sorting asc it still would be ranked at the bottom.  Now if you went with text then you run it sorting problems.  Where 2:30:45 would be ranked behind 12:30:35.

And as far as displaying purposes you can strip of the AM/PM.

Avatar of crazyman
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Do your sort and compare when you read the data back in to the program from the database.

Use this for the SQL statement:
SELECT TOP 10 * from table ORDER BY RunningTime DESC

Avatar of geoffsweb


Do I need to make sure the time is entered into the db always with AM or PM... obviously that would throw the sort off.  ??
It wont affect the sort either way, the database stores is the same.  It may be easier from a coding standpoint to use 24 hour time (where 1pm = 13:00) so you dont have to worry about the am/pm, but the databse won't know the difference..
No don't enter it with AM/PM.  It will take care of that for you.
What happens if somebody runs a race that takes them 26 hrs ?  so 26:10:05
Trust me, they wouldn't even notice the time if they did. lol.  But seriously if that were the case then it would present a problem.  But I'm assuming (I know not good) that the race would be no longer then 24hrs.  If my assumption is wrong then you should diffently go with crazyman's comment.
ICINTRACOM: there are 100 mile races that crazy people run (they span multiple days) !  But I think for now I won't allow them to enter those races/times in !
If the times can be longer than 24 hours, I would suggest using an integer rather than a datatime field.  Convert the runtumes to seconds before storing in the database.

If the hours, minutes and seconds are entered into seperate text boxes that is simple, or if they are in a single box in the hh:mm:ss format you can do a string.split on the colon.

int runtime = (hours*3600)+(minutes*60)+seconds

use modulus to get the times back out
int hours = runtime /3600
int minutes = (runtime % 3600)/60
int seconds = ((runtime % 3600) % 60)
Not sure why you would want to store as datetime, this is really a timespan so am/pm doesnt come into it...
oh and excuse my comment i missed a 0 out should be 3600 not 360.
Why not create [Start] and [End] DateTime fields in the database? Then you can use the DateDiff method to calculate whatever you want.  You could also just build it into your MS Access query as shown here:

Example Query:
SELECT TOP 10 DateDiff("s",[Start],[End]) AS RunningTime FROM myTable ORDER BY RunningTime DESC

Whether you store the seconds in the database or whether you decide to use dates, this function will produce a days:hours:minutes:seconds format for the seconds in the running time:

Protected Function RunningTime(s as Double) As String
  Date.Parse(Date.FromOADate(Seconds / (60 * 60 * 24))).ToString("dd:hh:mm:ss")
End Function

RunningTime(12056230025) = 15:04:47:05

I ended up storing them as total seconds in the db, but then I need to convert this to hours, minutes, and seconds when I pull it out of the database.  Can I do this conversion in my sql statement that is binding the datagrid?  Or where can I do it to populate the datagrid?
You could do it in your SQL or you can create a template column in the datagrid and doing the conversion using the function I provided earlier.  Personally, I am more comfortable with code that SQL statements so I would probably go with the code.  Here is a SQL example I found:

SELECT CONVERT(varchar(6), Diff/3600)
+ ':' + RIGHT('0' + CONVERT(varchar(2), (Diff % 3600) / 60), 2)
+ ':' + RIGHT('0' + CONVERT(varchar(2), Diff % 60), 2)

Found at:

If you go with the SQL method is may be harder to correctly sort by running time as the resulting conversion is a string and no longer numeric.  Just something to consider...
I would rather handle it in the code too, but I am a little unsure as to where to access/go about  changing the code from.
Never mind, I figured it out.... I was looking for this: _ItemDataBound

Thanks for all the help !!
The easiest way would be to add a template column to your datagrid in the ASPX page.

<asp:DataGrid runat="server" id="gridData">
                Running Time
                <%# RunningTime(Eval("Seconds").ToString) %>

Or, if you really really want it all in code, you can do the same thing programatically as outlined in this article: