Multiply time by hourly rate

Question

Post reply

Multiply time by hourly rate

pedroccamara

SSC-Addicted

Points: 431
More actions
November 25, 2022 at 5:21 pm

#4118055

Hello all,
I have this table with time for each row. It shows hh:mm.
I have rows with all kind of time, 6:30, 5:10, 5:00, 6:42 etc, and I have to multiply it by the hourly rate.
Could you please help me?
I think I have to, first convert this time column into a number so I can multiply it with the hourlyRate, right? How do i do this in mysql?
OR, is it better to split and add both after the multiplication? Can you help me on how to do it?
Thank you all in advance

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

pedroccamara SSC-Addicted Points: 431 More actions · Answer 1

Found it, never mind

substring_index(TT.horas,':',1)*60+substring_index(TT.horas,':',-1),

Thanks

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 2

pedroccamara wrote:

Found it, never mind
substring_index(TT.horas,':',1)*60+substring_index(TT.horas,':',-1),
Thanks

The SUBSTRING() stuff will make it slower than you need to. Here's a way to easily convert such "time strings" to Decimal hours.

 SELECT DATEDIFF(mi,0,'6:42')/60.0

Just replace the '6:42' with the TT.horas column.

If TT.horas isn't a character based column, post back with what datatype the column is.

EDIT... I AM assuming that you have no times over 24 hours. The code above will tank if the time represents 24 or more hours.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

I just noticed that "substring_index" isn't the name of a alias here... it's the name of a function and that function doesn't exist in SQL Server. You should let people know when you're using MySQL because things like DATEDIFF() don't work quite the same way in other SQL Dialects as they do in SQL Server.

What I said, still holds true.... string conversions will still be slower and more resource intensive than temporal functions will be. I also don't know if MySql has the same temporal epoch as SQL Server nor whether the DATEDIFF() there will understand that "0" is the interger DateSerial# for the first of January, 1900.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

pedroccamara SSC-Addicted Points: 431 More actions · Answer 4

Hello Jeff and thank you very much for your answer.

You're right. My mistake. I thought I was in a MySql forum.

About the issue....you're sure that the function to convert that field is the DATEDIFF? It gives me an error:

Error Code:1582. Incorrect parameter count in the call to native function 'DATEDIFF'

And TT.horas is a text column because if I convert it to time it will show instead of 8:00, 08:00:00

The purpose, I think, it's to convert it to minutes or to a number. Can you help?

This reply was modified 1 year, 12 months ago by pedroccamara.

frederico_fonseca SSCoach Points: 16154 More actions · Answer 5

you really need to read the manuals for MySQL - datediff in Mysql is totally different of that for SQL Server hence the error you get.

in mysql you would need to use function TIME_TO_SEC(timeexpression) to convert to seconds - and then its easy to do mathematical operations as you need.

SELECT TIME_TO_SEC(concat("00:","01:00")) * (12000 / 3600) ; where 12000 is the hourly rate

pedroccamara SSC-Addicted Points: 431 More actions · Answer 6

Hello Frederico

Thanks a lot for your help

It worked perfect!!!

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 7

pedroccamara wrote:

Hello Jeff and thank you very much for your answer. You're right. My mistake. I thought I was in a MySql forum.
About the issue....you're sure that the function to convert that field is the DATEDIFF? It gives me an error:
Error Code:1582. Incorrect parameter count in the call to native function 'DATEDIFF'
And TT.horas is a text column because if I convert it to time it will show instead of 8:00, 08:00:00
The purpose, I think, it's to convert it to minutes or to a number. Can you help?

Like I said, DATEDIFF() is quite different in MySQL than it is in SQL Server. I was going to lookup the replacement in the function documentation but it Frederico beat me to it, good an proper.

As a bit of a sidebar, it's better to store date and times (even in MySQL) as a date and time datatype so that you can do calculations with it instead of splitting strings, etc, etc. When you need to display it in a certain format, that's when you would do some sort of text conversion.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 8

frederico_fonseca wrote:

you really need to read the manuals for MySQL - datediff in Mysql is totally different of that for SQL Server hence the error you get.
in mysql you would need to use function TIME_TO_SEC(timeexpression) to convert to seconds - and then its easy to do mathematical operations as you need.
SELECT TIME_TO_SEC(concat("00:","01:00")) * (12000 / 3600) ; where 12000 is the hourly rate

Nice!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)