MySQL TIMEDIFF Query to calculate elapsed minutes within business hours

Databases
1

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.

How do I go best ahead with this ?

Any help is greatly appreciated. Cheers.

2

want my advice? don’t try to do this with sql

:slight_smile:

3

Thanks for the reply r937

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

4

you could write a user-defined function in mysql, yeah

same order-of-magnitude difficulty as writing it in php, i’m guessing

in either case, it’s not actually being done with sql, though, and that was my point

5

Gotcha. I guess I’ll head towards the PHP guys then and ask them to have a look at my script ^^

Thanks mate.

6

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.

7

though it may not look that way on a small amount of data or on a server that isn’t getting much traffic.

Unfortunately we’re talking about a multi-million-row db :sick:

On the bright side: It’s only supposed to be accessed by a single user on a local machine so bandwidth is none of my concern.

Its a good idea to pull the information needed using SQL and then process the data using your programming language of choice.

And I’m about to do just that ^^

I’ve just posted a script over at the PHP section and I’m sure someone will have a clue or two how to give it the final touch.