SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL TIMEDIFF Query to calculate elapsed minutes within business hours

    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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    want my advice? don't try to do this with sql

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Gotcha. I guess I'll head towards the PHP guys then and ask them to have a look at my script ^^

    Thanks mate.

  6. #6
    SitePoint Zealot j3ph's Avatar
    Join Date
    Jan 2006
    Posts
    134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SitePoint Member
    Join Date
    Jan 2010
    Posts
    7
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •