Page 1 of 1
Elapsed TIME manipulation
Posted: Fri Feb 13, 2009 11:15 am
by Dave2000
OK, I have an elapsed time stored in mysql. But how do I get a fraction of this time? For example, if the time was 1:31:01, how do I divide it by 3 or divide it by 4? Is there a function for this? I can't seem to find one. Only thing I can think of is:
,SEC_TO_TIME(TIME_TO_SEC(elapsedTime)/3)
or
,SEC_TO_TIME(TIME_TO_SEC(elapsedTime)/4)
But this seems a bit clunky. Is this the "official" way to do it?
S
Re: Elapsed TIME manipulation
Posted: Fri Feb 13, 2009 7:15 pm
by josh
Convert to timestamp & do it in PHP
Re: Elapsed TIME manipulation
Posted: Fri Feb 13, 2009 7:28 pm
by VladSun
josh wrote:Convert to timestamp & do it in PHP
What about using this 1/3 (or 1/4) value in a SQL query - one should not use PHP for "regenerating" the appropriate query.
It's true that the OP is not clear, so:
Shears, could you explain us the purpose of the query and what you are trying to achieve.
Re: Elapsed TIME manipulation
Posted: Sun Feb 15, 2009 9:35 am
by Dave2000
The table contains a "timeDuration" field of TIME type, and a "numBooks" field of INT type.
For each row I am trying to find a timeDuration per book value, ie. "timeDuration / numBooks".
However, as you probably know, I can't just divide a TIME type field by an INT type field.
[sql]SELECT timeDuration, numBooks, SEC_TO_TIME(TIME_TO_SEC(timeDuration)/numBooks) FROM customer[/sql]
The only way I can think of doing it as above. I was just wondering if there was a better "official" way..?
Thanks
S
Re: Elapsed TIME manipulation
Posted: Sun Feb 15, 2009 12:25 pm
by VladSun
While your query is not wrong in any way, I do think that your DB design is somehow wrong.
You didn't provide much information, so I suppose it is a public library (or similar) software.
I would expect that for every book rent you have a record in your DB and you put the time duration it has been rented. Having this data you can calculate the time duration per book by using the AVG() (average) SQL function:
[sql]SELECT id, title, avg(duration) AS time_per_bookFROM bookGROUP BY id, title[/sql]
Having a structure like yours (denormalized) is appropriate only in huge DBs - millions of records and the denormalization is done by scheduled jobs.
PS: You could use unix timestamp field type instead of date-time type to avoid conversion to seconds.