trouble Storing and retrieving hhh:mm:ss data

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mckooter
Forum Commoner
Posts: 26
Joined: Fri Jul 28, 2006 10:02 pm

trouble Storing and retrieving hhh:mm:ss data

Post by mckooter »

To start: I create/maintain a website for an online game, I am currently nearly done with a script that parses a simple html file that is output by the server with character information on it. And then retrieves it in another php file (it allows me to a] sort that data and b] select individual pieces for other projects) I have the parser and the display scripts written but am coming in trouble with managing with a field called: "Total Time Online"

The problem is that the data is stored in hhh:mm:ss format, so it could include the following:
157:24:08
2:15:56
10:46:55
etc...
when i created everything I simply stored the entire string as a varchar in my mysql server. but have come to realize this is horribly inefficient as i cannot sort the data appopriately, when sorting from largest to smallest I run into the following:
8:06:39
8:02:01
89:53:51
so it is obviously thinking that 8 is larger than 89, most definately because im using a string rather than an appropriate variable type. I was looking into the TIME type in MySQL but it only supports up to 838:59:59, which seems large enough but its only like 34 days and some players could possibly exceed that.

I looked into a ereg_replace or preg_replace alternative as I dont need to store the data as time because i will not be making any calculations with it, the problem is that I do not know how many digits would be in the hour field, it could be hhh:mm:ss or h:mm:ss so i do not belive that would work when trying to convert back to hhh:mm:ss from hhhmmss.

what would be the best way to achieve this, i need to be able to store the data, then retrieve it in the hhh:mm:ss format and have it be sortable.

sorry for the long winded post, i just wanted to make sure that I explained everything, ive been searching and cannot come up with a suitable solution.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: trouble Storing and retrieving hhh:mm:ss data

Post by Kieran Huggins »

what are you storing? You could store the length of seconds and work it out from there. Or a start datetime and an end datetime, then get the difference in the query?
Post Reply