Average time difference, TIMEDIFF

PHP
1

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

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 :slight_smile:

2

I can’t see where I can edit my post?

Anyways, this displays hours, minutes and seconds:

$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

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

4

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

me? nope, sorry, i only do coldfusion, not php :slight_smile:

6 

$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 ( :google: it :slight_smile: )

:slight_smile:

Off Topic:

I’m moving this thread to the PHP forum now

7

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 :slight_smile:

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

Thank you so much!!

8

You’re welcome :slight_smile: