Multiply time by hourly rate

  • 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

  • Found it, never mind

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

    Thanks

  • 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)

  • 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)

  • 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 2 months, 1 week ago by  pedroccamara.
  • 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

  • Hello Frederico

    Thanks a lot for your help

    It worked perfect!!!

  • 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)

  • 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)

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

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