2. PHPRunner General questions
  3. [SOLVED]  SQL Query question
This topic is locked
[SOLVED]

 SQL Query question

 		3/16/2017 1:31:35 PM
PHPRunner General questions
H
 Hertz2P author

I use this code to calculate my billing hours, but I'd much rather find a way to do it that doesn't round the number up.. Is there an easy way to write the same expression so that it just throws away the following digit instead of rounding? I want to bill for my time in tenth hour (6 minute) increments, starting at .1 as soon as I start a timer. My problem is that once I hit 3 minutes, it rounds up and I'm billing for 12 minutes. I want it to stay at 6 minutes until I start the seventh minute, then bill for 12 (or .2 hours.)
Here is the code that works, but rounds up at .05:

round (time_to_sec(timediff(`end time`,`start time`))/ 3600,1)+.1 AS `total time`,


Thanks
HJB 3/16/2017

http://stackoverflow.com/questions/5608967/how-to-add-5-minutes-to-current-datetime-on-php-5-3
For inspiration purposes only. Anything around the topic ROUNDING has TWO walls to hit, say,

next lower and next upper value, example: you have 13 minutes, then a ROUNDING code would then

downturn to 12 minutes as next 18 minutes (upper) is too far away by means of rounding logics.
What you are looking is something else, say, on minute 7 the billing needs to show 12, yet any

rounding formula would downturn your 7 to 6 as rounding between 6 and 12 means that 9 would

be the middle axis between upper and lower rounding result to go.
Not ROUNDING, but only ADDITION can solve your problem.
H
 		Hertz2P author 3/16/2017



http://stackoverflow.com/questions/5608967/how-to-add-5-minutes-to-current-datetime-on-php-5-3
For inspiration purposes only. Anything around the topic ROUNDING has TWO walls to hit, say,

next lower and next upper value, example: you have 13 minutes, then a ROUNDING code would then

downturn to 12 minutes as next 18 minutes (upper) is too far away by means of rounding logics.
What you are looking is something else, say, on minute 7 the billing needs to show 12, yet any

rounding formula would downturn your 7 to 6 as rounding between 6 and 12 means that 9 would

be the middle axis between upper and lower rounding result to go.
Not ROUNDING, but only ADDITION can solve your problem.


I already have the ADDITION in the code above. I start out by adding .1 to the timer. Here's a couple cases to help explain it better.
Case 1: Let's say I work for 5 minutes. Ideally, I would like the formula to throw that away as it has not hit the 6 minute threshold. If that were to happen, the +.1 would generate a timer that totals .1 hours, or 6 minutes. The code I use now would be .2 hours or 12 minutes.
Case 2: Let's say I work for 7 minutes. My code has the desired result here, it rounds down to .1 hour, and then adds another .1 hour to it, and the timer reflects .2 hours or 12 minutes. The issue doesn't kick in until 9 minutes when it will round up to .2 and the added .1 brings it to .3 hours.
What I'm looking to do is to always "round down" or throw away the 1/100's of an hour digit rather than using its data to round up. I thought I remembered there being a command for that, but I haven't been able to get anything to work within my project.
Thanks
HJB 3/17/2017

Try this function:

function blockMinutesRound($hour, $minutes = '6', $format = "H:i") {

$seconds = strtotime($hour);

$rounded = round($seconds / ($minutes * 60)) * ($minutes * 60);

return date($format, $rounded);

}
//call

blockMinutesRound('20:07');// return 20:06
H
 		Hertz2P author 3/18/2017



Try this function:

function blockMinutesRound($hour, $minutes = '6', $format = "H:i") {

$seconds = strtotime($hour);

$rounded = round($seconds / ($minutes * 60)) * ($minutes * 60);

return date($format, $rounded);

}
//call

blockMinutesRound('20:07');// return 20:06



I'm pretty sure this isn't SQL code, but I'm not sure where I would put this. My original code is a simple SQL string that I wrote on the Query page.
HJB 3/18/2017



I'm pretty sure this isn't SQL code, but I'm not sure where I would put this. My original code is a simple SQL string that I wrote on the Query page.


https://xlinesoft.com/phprunner/docs/_view_as__settings_custom.htm
That's right, PHP code doesn't care about SQL, yet as long as it is delivering what you need, I think it to be okay.
Y
 		YCH 3/20/2017

@Hertz2P
If you want to use SQL, you could use the 'DIV' function. This function discards from the division result any fractional part to the right of the decimal point.

For your purpose use this : Time (in seconds) DIV 360 ( = 6 minutes or 0.1 hour)
say 3 minutes : result --> 180 DIV 360 will return 0 then add 1 to the result . Final result = 1 ( = 1 unit of 6 minutes)

say 7 minutes : result --> 420 DIV 360 will return 1 then add 1 to the result . Final result = 2 ( = 2 units of 6 minutes)
H
 		Hertz2P author 3/20/2017



@Hertz2P
If you want to use SQL, you could use the 'DIV' function. This function discards from the division result any fractional part to the right of the decimal point.

For your purpose use this : Time (in seconds) DIV 360 ( = 6 minutes or 0.1 hour)
say 3 minutes : result --> 180 DIV 360 will return 0 then add 1 to the result . Final result = 1 ( = 1 unit of 6 minutes)

say 7 minutes : result --> 420 DIV 360 will return 1 then add 1 to the result . Final result = 2 ( = 2 units of 6 minutes)


YCH, this is what I was trying to remember! I knew there was a function that throws away the remainders..
So it took a little playing around, and a lot of parenthesis to make it work correctly in my generated application, but it eventually did exactly what I was asking for.
For those interested, here is the working SQL code for what I needed:

((time_to_sec(timediff(`end time`,`start time`))DIV 360)*.1)+.1 AS `total time`,

XXX*(((time_to_sec(timediff(`end time`,`start time`))DIV 360)*.1)+.1) AS `total money`,


As you can see, I had to multiply by .1 at the end to make it work with the rest of my project, because dividing by 3600 didn't give me the desired results. Using the code on the bottom, you can put your hourly billing rate into the 'XXX' and it will give you a subtotal for that period of time.
It's pretty sweet, now when the phone rings, I start a timer. When I hang up, I end it, and generate and email an invoice. The customer has it 30 seconds after I hang up. I can do the same thing when I arrive at a consulting or repair job. Start a timer on my phone, etc.. Customer has their invoice before I leave..
It 'worked' before, but I always felt like it was 'cheating' customers. Now it is in compliance with my billing policies (tenth minute billing, starting at 1.)
Thanks to both of you for the help, this one is solved <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=81696&image=1&table=forumreplies' class='bbc_emoticon' alt=';)' />