Hi all. I’ve just registered to this forum because I’ve been searching for days now, and while often getting close, I couldn’t really resolve the issue I#m dealing with.
I’ve got a table with two DATETIME values in each row. What I’d like to do is, is crafting a query that calculates the TIMEDIFF between the two dates, but only the elapsed time within business hours, e.g. between 08:00 and 17:00. Furthermore Saturday and Sunday need to be excluded.
The closest I’ve come is with a PHP script, but I would like to do it in MySQL.
Currently I have an Access / MSVB application that does just what I want, but I’m not a fan of this software vendor and I am currently in the process of rewriting the application using open source software only … thus it’s PHP / MySQL based and runs in a browser.
So far it really works like a charm as far as it concerns the importing operations of the data, the creation of the numerous tables and it already delivers the output I want to … except this time calculation thingy.
Are you saying I better pull the data via PHP from the db and do the calculations only within the PHP script ? Isn’t there a way to program a function in MySQL that does this ? In MS Access the VB function is called from within the Query. I was hoping to gain a huge performance improvement by not doing that in PHP ; )
Any major calculations done inside of an SQL query are going to slow your queries down considerably, though it may not look that way on a small amount of data or on a server that isn’t getting much traffic.
Its a good idea to pull the information needed using SQL and then process the data using your programming language of choice.