Should I change from the TIME datatype?

[Jay NOYB]

I'm working on a database for a fighting league.

Currently I'm using the datatype TIME for the time in the round in which the fight ended. However, I'm finding the "00:05:00" thing annoying (although, I've truncated it to 05:00). There's also another problem, adding the time up seems to be a total pain in the ass.

So I'm wondering - would I be better off switching the TIME data type and just making 2 separate columns for Minutes and Seconds?

So instead of storing 00:02:23, I'd store 2 and 23. This way I can easily show it the way I want to, and it seems I'd perhaps have an easier time later on adding up the columns.

What I want to be able to do is add up the minutes and seconds and show the total amount of fighting time that fighter has spent in the ring.

The TIME datatype seemed like a good idea in the beginning, but as I'm working with the database, it now seems like the wrong way to store the data for how I want to use it.


-------------SOLVED----------------
I've solved below, should I need to do it, I simply did this: "UPDATE TEST SET TInt = Time_Format(TTime, '%i')" and it copied over just the minute to the new column, so I can do that for seconds as well. I left this here for others who might see it and be wondering the same thing. So you can ignore below.

If it is a good idea to just change it from TIME to two separate columns for minutes and seconds, is there a way to make sql read the old column data and place it in the two?

The TIME is ALWAYS only in minutes and seconds - maxing out at 5:00, so it's never 12:03:40 or whatever, the max is "00:05:00"

is there a way I can tell mysql (I'm using the admin gui controls thing [I'm hosting on godaddy]) to take the minutes from the TIME column and place it in the new Minutes column, and then take the Seconds from the TIME column and place it in the new Seconds Column?

Since there's a way to format it to show just the time segments you want using the % commands, I know you can tell mysql how to read the TIME type - so I'm assuming there's a way to tell it to store the Minutes and Seconds columns separately, and then copy them to a specific column - I just have no idea how...

Or am I going to have to make two new columns, manually look at the TIME column for each fight (of which there are under 1,000 of right now, so it's not a huge deal to have to do), and insert the data into the columns myself for each one?



Edited 3 time(s). Last edit at 05/05/2009 09:54AM by Jay NOYB.