SitePoint Sponsor

User Tag List

Results 1 to 8 of 8
  1. #1
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Average time difference, TIMEDIFF

    I have a query but am unsure of how to output it in PHP.

    Code MySQL:
    SELECT SEC_TO_TIME ( AVG ( TIMESTAMPDIFF ( SECOND, 'datecontacted','dateadded'))) FROM leads WHERE userid = $userid AND firstcontactdate != '0000-00-00 00:00:00'

    Any help would be appreciated

  2. #2
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I can't see where I can edit my post?

    Anyways, this displays hours, minutes and seconds:

    Code PHP:
    $query = mysql_query("SELECT SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, `firstcontactdate`,`dateadded`))) FROM leads WHERE userid = $userid AND firstcontactdate != '0000-00-00 00:00:00'");
    $queryresult = mysql_fetch_array($query);
    echo $queryresult[0];

    It would be good if I could get it to display the actual text 'hours', 'minutes' and 'seconds' too, any suggestions? I'm not familiar with MYSQL, I've looked at the dev guides.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    a good argument could be made for passing only the result of TIMESTAMPDIFF back, but nice job using SEC_TO_TIME

    however, any further formatting should be done with application code, not in the query
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    a good argument could be made for passing only the result of TIMESTAMPDIFF back, but nice job using SEC_TO_TIME

    however, any further formatting should be done with application code, not in the query
    I'm really a massive noob when it comes to PHP and MYSQL, only played around it with in the last few weeks. May I ask how you think I should go about formatting it with application code? Currently I am having to work with:

    'Your average contact time is 30:39:32 (hours, minutes, seconds)'

    Thanks

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by jamb0 View Post
    May I ask how you think I should go about formatting it with application code?
    me? nope, sorry, i only do coldfusion, not php
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    PHP Code:
    $query mysql_query("SELECT SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, `firstcontactdate`,`dateadded`))) FROM leads WHERE userid = $userid AND firstcontactdate != '0000-00-00 00:00:00'");
    $queryresult mysql_fetch_array($query);
    list(
    $hours$minutes$seconds)=explode(':'$queryresult[0]);

    echo 
    $hours ' hour' . ($hours!=1?'s':'') . ', '.$minutes . ($minutes!=1?'s':'') . ' and ' $seconds.' second'.($seconds!=1?'s':''); 
    The ($seconds!=1?'s':'') bit is kinda the same as if ($seconds != 1) { echo 's'; } else { echo ''; }. It's known as the ternary operator ( it )



    Off Topic:


    I'm moving this thread to the PHP forum now
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy

  7. #7
    SitePoint Member
    Join Date
    Apr 2011
    Posts
    8
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by ScallioXTX View Post
    PHP Code:
    $query mysql_query("SELECT SEC_TO_TIME(AVG(TIMESTAMPDIFF(SECOND, `firstcontactdate`,`dateadded`))) FROM leads WHERE userid = $userid AND firstcontactdate != '0000-00-00 00:00:00'");
    $queryresult mysql_fetch_array($query);
    list(
    $hours$minutes$seconds)=explode(':'$queryresult[0]);

    echo 
    $hours ' hour' . ($hours!=1?'s':'') . ', '.$minutes . ($minutes!=1?'s':'') . ' and ' $seconds.' second'.($seconds!=1?'s':''); 
    The ($seconds!=1?'s':'') bit is kinda the same as if ($seconds != 1) { echo 's'; } else { echo ''; }. It's known as the ternary operator ( it )



    Off Topic:


    I'm moving this thread to the PHP forum now
    The output seems to be in this format:

    '-30 hours, 39s and 32 seconds'

    I've added 'minutes' so am just looking at what to swap around to move the minus sign.

    Thanks very much

    EDIT: Just swapped the two fields around in the TIMESTAMPDIFF( section and the minus sign has gone.

    Thank you so much!!

  8. #8
    Utopia, Inc. silver trophy
    ScallioXTX's Avatar
    Join Date
    Aug 2008
    Location
    The Netherlands
    Posts
    8,892
    Mentioned
    138 Post(s)
    Tagged
    2 Thread(s)
    You're welcome
    Rémon - Hosting Advisor

    Minimal Bookmarks Tree
    My Google Chrome extension: browsing bookmarks made easy


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
  •