Calculating price based on time (time * hourly rate)

[Anonymous]

Hello everyone!

After doing several (unfortunately unsucessful ) searches on this forum I have decided to post my question here directly.

I am making a system that will automatically send time logs to (recreational) pilots and that will automatically calculate the amount they have to pay based on the total amount of hours they have flown (over a certain period of time). In order to focus on my question please consider the following database structure:

The database name is "database_time" and the table name is "table_time" (pretty self-explanatory)

mysql> SELECT * FROM database_time.table_time;

+-------------+-------------+
| flight_date | flight_time |
+-------------+-------------+
| 2004-05-05  |    01:35    |
| 2004-06-12  |    00:15    |
| 2004-06-15  |    00:35    |
| 2004-06-23  |    02:10    |
| 2004-07-01  |    00:45    |
| 2004-07-08  |    00:50    |
+-------------+-------------+

I basically have two questions:

1. How can I add all flight times together to get the total amount of time flown from (for instance) 2004-06-15 (including this date) until now?
   (I have searched the MySQL manual date and time functions and found a function "ADDTIME(expr,expr2)" that I thought might have something to do with this.)
   
2. How can I multiply the value received from 1 with an hourly rate of (let's say) $ 125,-- to get the total amount that has to be payed?
   My biggest problem in this situation was the fact that MySQL (or PHP for that matter) needs to convert a format like 02:30 to 2,5 in order to get an accurate calculation.

I would very much appreciate it if anyone can try to help me with this. Even though it might as well be a very easy situation for you guys, it has caused me quite a lot of headaches the past few days... :-D Thanks for your time!

Maurits

 

[Anonymous]

Well 1 minute is 1/60th of an hour. So divide the patial hour by 60.

If it's 1hr and 35min is 35min is like .5833 of an hour so $rate * 1.583 = $amount owed.

 

[Anonymous]

I only have one bit of advice for you: ditch the split time format and just store your times in minutes. You don't gain anything by splitting up your hours and minutes, and if you store them in minutes (1 hours = 60 minutes) it becomes much more easy to do math and build WHERE clauses.