CODEWITHSUNDEEP
mysql
Roddy McAskill
Roddy McAskill

Reputation: 23

Multiplying Hours & Mins by Hourly Rate

I have a MySQL database that records the number of hours worked on various clients. It has two tables, one storing the hours, and another holds the different rates I charge for work at each client.

I have a webpage that presents the data in a tabled format showing Start / Stop Time, Activity, Calculated Hours, Rate and a couple of other info calls.

The Hours column is calculated using TIME_FORMAT( TIMEDIFF( end_date, start_date ) , '%h %i' ) AS Hours which works fine, giving me the hours/mins worked. However the Amount column is only showing the Hours * Rate calculation rather than Hours&Min*Rate.

How do I include the minutes?

TIME_FORMAT( TIMEDIFF( end_date, start_date ) , '%h %i' ) * rate ... is what I am using right now to calculate the total amount but as I said the minutes value is ignored.

Appreciate any help, regards Roddy

Upvotes: 2

Views: 2314

Answers (4)

JoForest
JoForest

Reputation: 1

If your total time value is stored in format 00:00 and the hourly rate in format 00.00 you can use the following query to return value with 2 decimals;

SELECT ROUND(SUM( TIME_TO_SEC('time') * ('hourly_rate')))/60/60 AS 'total_value' FROM 'table'

Upvotes: 0

Strawberry
Strawberry

Reputation: 33945

SELECT TIME(NOW());
+-------------+
| TIME(NOW()) |
+-------------+
| 16:29:13    |
+-------------+

SELECT TIME_TO_SEC(TIME(NOW()))/3600;
+-------------------------------+
| TIME_TO_SEC(TIME(NOW()))/3600 |
+-------------------------------+
|                       16.4875 |
+-------------------------------+

Upvotes: 0

likeitlikeit
likeitlikeit

Reputation: 5638

Instead of TIMEDIFF, use TIMESTAMPDIFF which gives you the difference in a measure that you specify (seconds, hours, years). Use it like this in your query: 

SELECT TIMESTAMPDIFF( MINUTE , start_date, end_date ) * ( rate / 60 ) AS billable_amount
FROM  `your_table`

Upvotes: 3

C3roe
C3roe

Reputation: 96382

'%h %i' will result in something like 17 15 – that is a string value, and not something that can be multiplied with another number.

Use TIMESTAMPDIFF with unit MINUTE instead of TIME_FORMAT, or, if that doesn’t work (because according to docs it needs full date values), TIME_TO_SEC (and divide by 60).

Upvotes: 2

Related Questions