Mixing
MYsql - Get average time between a start and stop time across many rows
0
I have the following SQL and PHP that doesnt seem to be giving me the right numbers. (VERY high numbers)
I have a lot of rows with a start time and an end timestamp.
Im looking to get the average time between those two times.
Ie: 2 hours, 3 minutes, 46 seconds.
This is what I have.
SELECT AVG(tmp.dd) AS timetook
FROM
( SELECT TIME_TO_SEC(TIMEDIFF(timeclosed, timeanswered)) AS dd
FROM logs
WHERE timeclosed > DATE_SUB(NOW(), INTERVAL 1 DAY)
) tmp;
Am I going about this the completely wrong way? Anything obviously wrong here?
while($row = $result->fetch_assoc()) {
$timetoclose = $row['timetook'];
$hours = floor($timetoclose / 3600);
$mins = floor($timetoclose / 60 % 60);
$secs = floor($timetoclose % 60);
$timetoclose = sprintf('%02d Hour(s), %02d Minute(s), %02d Second(s)', $hours, $mins, $secs);
}
Cheers
G
mysql average
edited Jan 2 at 12:02
Strawberry
26.8k83250
asked Jan 2 at 11:53
Graham SmartGraham Smart
115
add a comment |
0
I have the following SQL and PHP that doesnt seem to be giving me the right numbers. (VERY high numbers)
I have a lot of rows with a start time and an end timestamp.
Im looking to get the average time between those two times.
Ie: 2 hours, 3 minutes, 46 seconds.
This is what I have.
SELECT AVG(tmp.dd) AS timetook
FROM
( SELECT TIME_TO_SEC(TIMEDIFF(timeclosed, timeanswered)) AS dd
FROM logs
WHERE timeclosed > DATE_SUB(NOW(), INTERVAL 1 DAY)
) tmp;
Am I going about this the completely wrong way? Anything obviously wrong here?
while($row = $result->fetch_assoc()) {
$timetoclose = $row['timetook'];
$hours = floor($timetoclose / 3600);
$mins = floor($timetoclose / 60 % 60);
$secs = floor($timetoclose % 60);
$timetoclose = sprintf('%02d Hour(s), %02d Minute(s), %02d Second(s)', $hours, $mins, $secs);
}
Cheers
G
mysql average
edited Jan 2 at 12:02
Strawberry
26.8k83250
asked Jan 2 at 11:53
Graham SmartGraham Smart
115
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 12:02
I see nothing obviously wrong in your query. Is the average of "2 hours" is THAT high? Over 24 hours, it doesnt look incorrect to me. Keep in my mind that with AVG, if you have a few rows with a high number, it increase everything. Maybe you should use MEDIAN instead, not available in MySQL but can mimick it
– Thomas G
Jan 2 at 12:32
add a comment |
0
0
0
I have the following SQL and PHP that doesnt seem to be giving me the right numbers. (VERY high numbers)
I have a lot of rows with a start time and an end timestamp.
Im looking to get the average time between those two times.
Ie: 2 hours, 3 minutes, 46 seconds.
This is what I have.
SELECT AVG(tmp.dd) AS timetook
FROM
( SELECT TIME_TO_SEC(TIMEDIFF(timeclosed, timeanswered)) AS dd
FROM logs
WHERE timeclosed > DATE_SUB(NOW(), INTERVAL 1 DAY)
) tmp;
Am I going about this the completely wrong way? Anything obviously wrong here?
while($row = $result->fetch_assoc()) {
$timetoclose = $row['timetook'];
$hours = floor($timetoclose / 3600);
$mins = floor($timetoclose / 60 % 60);
$secs = floor($timetoclose % 60);
$timetoclose = sprintf('%02d Hour(s), %02d Minute(s), %02d Second(s)', $hours, $mins, $secs);
}
Cheers
G
mysql average
edited Jan 2 at 12:02
Strawberry
26.8k83250
asked Jan 2 at 11:53
Graham SmartGraham Smart
115
I have the following SQL and PHP that doesnt seem to be giving me the right numbers. (VERY high numbers)
I have a lot of rows with a start time and an end timestamp.
Im looking to get the average time between those two times.
Ie: 2 hours, 3 minutes, 46 seconds.
This is what I have.
SELECT AVG(tmp.dd) AS timetook
FROM
( SELECT TIME_TO_SEC(TIMEDIFF(timeclosed, timeanswered)) AS dd
FROM logs
WHERE timeclosed > DATE_SUB(NOW(), INTERVAL 1 DAY)
) tmp;
Am I going about this the completely wrong way? Anything obviously wrong here?
while($row = $result->fetch_assoc()) {
$timetoclose = $row['timetook'];
$hours = floor($timetoclose / 3600);
$mins = floor($timetoclose / 60 % 60);
$secs = floor($timetoclose % 60);
$timetoclose = sprintf('%02d Hour(s), %02d Minute(s), %02d Second(s)', $hours, $mins, $secs);
}
Cheers
G
mysql average
mysql average
edited Jan 2 at 12:02
Strawberry
26.8k83250
asked Jan 2 at 11:53
Graham SmartGraham Smart
115
edited Jan 2 at 12:02
Strawberry
26.8k83250
asked Jan 2 at 11:53
Graham SmartGraham Smart
115
edited Jan 2 at 12:02
Strawberry
26.8k83250
edited Jan 2 at 12:02
Strawberry
26.8k83250
edited Jan 2 at 12:02
Strawberry
26.8k83250
26.8k83250
asked Jan 2 at 11:53
Graham SmartGraham Smart
115
asked Jan 2 at 11:53
Graham SmartGraham Smart
115
asked Jan 2 at 11:53
Graham SmartGraham Smart
115
115
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 12:02
I see nothing obviously wrong in your query. Is the average of "2 hours" is THAT high? Over 24 hours, it doesnt look incorrect to me. Keep in my mind that with AVG, if you have a few rows with a high number, it increase everything. Maybe you should use MEDIAN instead, not available in MySQL but can mimick it
– Thomas G
Jan 2 at 12:32
add a comment |
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 12:02
I see nothing obviously wrong in your query. Is the average of "2 hours" is THAT high? Over 24 hours, it doesnt look incorrect to me. Keep in my mind that with AVG, if you have a few rows with a high number, it increase everything. Maybe you should use MEDIAN instead, not available in MySQL but can mimick it
– Thomas G
Jan 2 at 12:32
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 12:02
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 12:02
I see nothing obviously wrong in your query. Is the average of "2 hours" is THAT high? Over 24 hours, it doesnt look incorrect to me. Keep in my mind that with AVG, if you have a few rows with a high number, it increase everything. Maybe you should use MEDIAN instead, not available in MySQL but can mimick it
– Thomas G
Jan 2 at 12:32
I see nothing obviously wrong in your query. Is the average of "2 hours" is THAT high? Over 24 hours, it doesnt look incorrect to me. Keep in my mind that with AVG, if you have a few rows with a high number, it increase everything. Maybe you should use MEDIAN instead, not available in MySQL but can mimick it
– Thomas G
Jan 2 at 12:32
add a comment |
1 Answer
1
active
oldest
votes
0
SELECT SUM(TIMESTAMPDIFF(minute, start_time, end_time)) / COUNT(*) AS avg_minutes
FROM your_table
try this query.
edited Jan 2 at 13:23
JochenJung
5,708105398
answered Jan 2 at 12:19
Mukitul IslamMukitul Islam
11
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54005886%2fmysql-get-average-time-between-a-start-and-stop-time-across-many-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
SELECT SUM(TIMESTAMPDIFF(minute, start_time, end_time)) / COUNT(*) AS avg_minutes
FROM your_table
try this query.
edited Jan 2 at 13:23
JochenJung
5,708105398
answered Jan 2 at 12:19
Mukitul IslamMukitul Islam
11
add a comment |
0
SELECT SUM(TIMESTAMPDIFF(minute, start_time, end_time)) / COUNT(*) AS avg_minutes
FROM your_table
try this query.
edited Jan 2 at 13:23
JochenJung
5,708105398
answered Jan 2 at 12:19
Mukitul IslamMukitul Islam
11
add a comment |
0
0
0
SELECT SUM(TIMESTAMPDIFF(minute, start_time, end_time)) / COUNT(*) AS avg_minutes
FROM your_table
try this query.
edited Jan 2 at 13:23
JochenJung
5,708105398
answered Jan 2 at 12:19
Mukitul IslamMukitul Islam
11
SELECT SUM(TIMESTAMPDIFF(minute, start_time, end_time)) / COUNT(*) AS avg_minutes
FROM your_table
try this query.
edited Jan 2 at 13:23
JochenJung
5,708105398
answered Jan 2 at 12:19
Mukitul IslamMukitul Islam
11
edited Jan 2 at 13:23
JochenJung
5,708105398
edited Jan 2 at 13:23
JochenJung
5,708105398
edited Jan 2 at 13:23
JochenJung
5,708105398
5,708105398
answered Jan 2 at 12:19
Mukitul IslamMukitul Islam
11
answered Jan 2 at 12:19
Mukitul IslamMukitul Islam
11
answered Jan 2 at 12:19
Mukitul IslamMukitul Islam
11
11
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54005886%2fmysql-get-average-time-between-a-start-and-stop-time-across-many-rows%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
See: Why should I provide an MCVE for what seems to me to be a very simple SQL query?
– Strawberry
Jan 2 at 12:02
I see nothing obviously wrong in your query. Is the average of "2 hours" is THAT high? Over 24 hours, it doesnt look incorrect to me. Keep in my mind that with AVG, if you have a few rows with a high number, it increase everything. Maybe you should use MEDIAN instead, not available in MySQL but can mimick it
– Thomas G
Jan 2 at 12:32