Elapsed TIME manipulation

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Elapsed TIME manipulation

Post 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
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Elapsed TIME manipulation

Post by josh »

Convert to timestamp & do it in PHP
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Elapsed TIME manipulation

Post 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.
Last edited by VladSun on Sun Feb 15, 2009 12:27 pm, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
Dave2000
Forum Contributor
Posts: 126
Joined: Wed Jun 21, 2006 1:48 pm

Re: Elapsed TIME manipulation

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Elapsed TIME manipulation

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply