mysql - query to fill in missing datetime values The 2019 Stack Overflow Developer Survey...
Identify 80s or 90s comics with ripped creatures (not dwarves)
What happens to a Warlock's expended Spell Slots when they gain a Level?
Is an up-to-date browser secure on an out-of-date OS?
Is every episode of "Where are my Pants?" identical?
What's the point in a preamp?
What force causes entropy to increase?
Working through the single responsibility principle (SRP) in Python when calls are expensive
Student Loan from years ago pops up and is taking my salary
Word for: a synonym with a positive connotation?
Sub-subscripts in strings cause different spacings than subscripts
Why are PDP-7-style microprogrammed instructions out of vogue?
Mortgage adviser recommends a longer term than necessary combined with overpayments
"is" operation returns false with ndarray.data attribute, even though two array objects have same id
Do working physicists consider Newtonian mechanics to be "falsified"?
Using dividends to reduce short term capital gains?
Was credit for the black hole image misappropriated?
Can I visit the Trinity College (Cambridge) library and see some of their rare books
My body leaves; my core can stay
Is there a way to generate uniformly distributed points on a sphere from a fixed amount of random real numbers per point?
What other Star Trek series did the main TNG cast show up in?
Keeping a retro style to sci-fi spaceships?
Didn't get enough time to take a Coding Test - what to do now?
Does Parliament need to approve the new Brexit delay to 31 October 2019?
Does Parliament hold absolute power in the UK?
mysql - query to fill in missing datetime values
The 2019 Stack Overflow Developer Survey Results Are In
Unicorn Meta Zoo #1: Why another podcast?
Announcing the arrival of Valued Associate #679: Cesar ManaraDrop auto increment hack w/o alter table?Are two indexes needed?table is full in innodbFinding rows for a specified date rangeCalculating employee's work timeAdding index to large mysql tablesWhy are simple SELECTs on InnoDB 100x slower than on MyISAM?Need help improving sql query performanceQuery monthly having group_concat and group by need helpSubquery and subselection for Count/Number doesnt work
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have a Table similar to:
CREATE TABLE `stats` (
`stat_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`stat_Start` timestamp NULL DEFAULT NULL,
`stat_values` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`stat_ID`),
);
It contains a list of events with the time it happenned, and the value.
I am now looking to generate a report from this similar to:
SELECT
LEFT(stat_start,16) AS `time`,
AVG(`stat_values`) AS `data`
FROM stats s
WHERE s.stat_Start BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()
GROUP BY LEFT(s.stat_start,16)
ORDER BY stat_start;
In other words, get the Average value for all entries per minute for the past hour.
However, in some cases I don't have an entry for some minutes, but I still need to know that there were 0 results for that minute.
Is there a way of achieving this in MySQL?
so I might get something like:
'2017-04-07 09:01', 102,
'2017-04-07 09:02', 0, --> row for which no events were recorded
'2017-04-07 09:03', 132
mysql timestamp
asked Apr 7 '17 at 10:26
IGGtIGGt
98711027
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
add a comment |
I have a Table similar to:
CREATE TABLE `stats` (
`stat_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`stat_Start` timestamp NULL DEFAULT NULL,
`stat_values` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`stat_ID`),
);
It contains a list of events with the time it happenned, and the value.
I am now looking to generate a report from this similar to:
SELECT
LEFT(stat_start,16) AS `time`,
AVG(`stat_values`) AS `data`
FROM stats s
WHERE s.stat_Start BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()
GROUP BY LEFT(s.stat_start,16)
ORDER BY stat_start;
In other words, get the Average value for all entries per minute for the past hour.
However, in some cases I don't have an entry for some minutes, but I still need to know that there were 0 results for that minute.
Is there a way of achieving this in MySQL?
so I might get something like:
'2017-04-07 09:01', 102,
'2017-04-07 09:02', 0, --> row for which no events were recorded
'2017-04-07 09:03', 132
mysql timestamp
asked Apr 7 '17 at 10:26
IGGtIGGt
98711027
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
The same problem answered here: stackoverflow.com/questions/24533485/…
– Tony H
Apr 7 '17 at 12:57
add a comment |
I have a Table similar to:
CREATE TABLE `stats` (
`stat_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`stat_Start` timestamp NULL DEFAULT NULL,
`stat_values` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`stat_ID`),
);
It contains a list of events with the time it happenned, and the value.
I am now looking to generate a report from this similar to:
SELECT
LEFT(stat_start,16) AS `time`,
AVG(`stat_values`) AS `data`
FROM stats s
WHERE s.stat_Start BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()
GROUP BY LEFT(s.stat_start,16)
ORDER BY stat_start;
In other words, get the Average value for all entries per minute for the past hour.
However, in some cases I don't have an entry for some minutes, but I still need to know that there were 0 results for that minute.
Is there a way of achieving this in MySQL?
so I might get something like:
'2017-04-07 09:01', 102,
'2017-04-07 09:02', 0, --> row for which no events were recorded
'2017-04-07 09:03', 132
mysql timestamp
asked Apr 7 '17 at 10:26
IGGtIGGt
98711027
I have a Table similar to:
CREATE TABLE `stats` (
`stat_ID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`stat_Start` timestamp NULL DEFAULT NULL,
`stat_values` smallint(5) unsigned DEFAULT NULL,
PRIMARY KEY (`stat_ID`),
);
It contains a list of events with the time it happenned, and the value.
I am now looking to generate a report from this similar to:
SELECT
LEFT(stat_start,16) AS `time`,
AVG(`stat_values`) AS `data`
FROM stats s
WHERE s.stat_Start BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()
GROUP BY LEFT(s.stat_start,16)
ORDER BY stat_start;
In other words, get the Average value for all entries per minute for the past hour.
However, in some cases I don't have an entry for some minutes, but I still need to know that there were 0 results for that minute.
Is there a way of achieving this in MySQL?
so I might get something like:
'2017-04-07 09:01', 102,
'2017-04-07 09:02', 0, --> row for which no events were recorded
'2017-04-07 09:03', 132
mysql timestamp
mysql timestamp
asked Apr 7 '17 at 10:26
IGGtIGGt
98711027
asked Apr 7 '17 at 10:26
IGGtIGGt
98711027
asked Apr 7 '17 at 10:26
IGGtIGGt
98711027
asked Apr 7 '17 at 10:26
IGGtIGGt
98711027
asked Apr 7 '17 at 10:26
IGGtIGGt
98711027
98711027
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
bumped to the homepage by Community♦ 12 mins ago
This question has answers that may be good or bad; the system has marked it active so that they can be reviewed.
The same problem answered here: stackoverflow.com/questions/24533485/…
– Tony H
Apr 7 '17 at 12:57
add a comment |
The same problem answered here: stackoverflow.com/questions/24533485/…
– Tony H
Apr 7 '17 at 12:57
The same problem answered here: stackoverflow.com/questions/24533485/…
– Tony H
Apr 7 '17 at 12:57
The same problem answered here: stackoverflow.com/questions/24533485/…
– Tony H
Apr 7 '17 at 12:57
add a comment |
1 Answer
1
active
oldest
votes
Filling in 'missing' rows is done via an extra table with all possible values, plus a LEFT JOIN:
Create have a table all_minutes with all possible minutes (in some finite range). (If using MariaDB, use a "Sequence" table.) Then
SELECT a.time,
IFNULL(s.data, 0) AS `data` -- assuming you want 0, not NULL
FROM all_minutes AS a
LEFT JOIN ( SELECT
LEFT(stat_start,16) AS `time`, -- eg, '2017-04-07 11:03'
AVG(`stat_values`) AS `data`
FROM stats
WHERE stat_Start BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
GROUP BY `time`
ORDER BY `time` -- note a change here
) AS s ON s.time = a.time
WHERE a.time BETWEEN ... AND ...; -- whatever range you need
Note: Both tables should exclude or include the :00 for seconds. Otherwise (I think) there could be some difficulty in doing the = in the ON.
answered Apr 7 '17 at 18:11
Rick JamesRick James
43.8k22259
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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
});
}
});
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%2fdba.stackexchange.com%2fquestions%2f169429%2fmysql-query-to-fill-in-missing-datetime-values%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
Filling in 'missing' rows is done via an extra table with all possible values, plus a LEFT JOIN:
Create have a table all_minutes with all possible minutes (in some finite range). (If using MariaDB, use a "Sequence" table.) Then
SELECT a.time,
IFNULL(s.data, 0) AS `data` -- assuming you want 0, not NULL
FROM all_minutes AS a
LEFT JOIN ( SELECT
LEFT(stat_start,16) AS `time`, -- eg, '2017-04-07 11:03'
AVG(`stat_values`) AS `data`
FROM stats
WHERE stat_Start BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
GROUP BY `time`
ORDER BY `time` -- note a change here
) AS s ON s.time = a.time
WHERE a.time BETWEEN ... AND ...; -- whatever range you need
Note: Both tables should exclude or include the :00 for seconds. Otherwise (I think) there could be some difficulty in doing the = in the ON.
answered Apr 7 '17 at 18:11
Rick JamesRick James
43.8k22259
add a comment |
Filling in 'missing' rows is done via an extra table with all possible values, plus a LEFT JOIN:
Create have a table all_minutes with all possible minutes (in some finite range). (If using MariaDB, use a "Sequence" table.) Then
SELECT a.time,
IFNULL(s.data, 0) AS `data` -- assuming you want 0, not NULL
FROM all_minutes AS a
LEFT JOIN ( SELECT
LEFT(stat_start,16) AS `time`, -- eg, '2017-04-07 11:03'
AVG(`stat_values`) AS `data`
FROM stats
WHERE stat_Start BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
GROUP BY `time`
ORDER BY `time` -- note a change here
) AS s ON s.time = a.time
WHERE a.time BETWEEN ... AND ...; -- whatever range you need
Note: Both tables should exclude or include the :00 for seconds. Otherwise (I think) there could be some difficulty in doing the = in the ON.
answered Apr 7 '17 at 18:11
Rick JamesRick James
43.8k22259
add a comment |
Filling in 'missing' rows is done via an extra table with all possible values, plus a LEFT JOIN:
Create have a table all_minutes with all possible minutes (in some finite range). (If using MariaDB, use a "Sequence" table.) Then
SELECT a.time,
IFNULL(s.data, 0) AS `data` -- assuming you want 0, not NULL
FROM all_minutes AS a
LEFT JOIN ( SELECT
LEFT(stat_start,16) AS `time`, -- eg, '2017-04-07 11:03'
AVG(`stat_values`) AS `data`
FROM stats
WHERE stat_Start BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
GROUP BY `time`
ORDER BY `time` -- note a change here
) AS s ON s.time = a.time
WHERE a.time BETWEEN ... AND ...; -- whatever range you need
Note: Both tables should exclude or include the :00 for seconds. Otherwise (I think) there could be some difficulty in doing the = in the ON.
answered Apr 7 '17 at 18:11
Rick JamesRick James
43.8k22259
Filling in 'missing' rows is done via an extra table with all possible values, plus a LEFT JOIN:
Create have a table all_minutes with all possible minutes (in some finite range). (If using MariaDB, use a "Sequence" table.) Then
SELECT a.time,
IFNULL(s.data, 0) AS `data` -- assuming you want 0, not NULL
FROM all_minutes AS a
LEFT JOIN ( SELECT
LEFT(stat_start,16) AS `time`, -- eg, '2017-04-07 11:03'
AVG(`stat_values`) AS `data`
FROM stats
WHERE stat_Start BETWEEN NOW() - INTERVAL 1 HOUR AND NOW()
GROUP BY `time`
ORDER BY `time` -- note a change here
) AS s ON s.time = a.time
WHERE a.time BETWEEN ... AND ...; -- whatever range you need
Note: Both tables should exclude or include the :00 for seconds. Otherwise (I think) there could be some difficulty in doing the = in the ON.
answered Apr 7 '17 at 18:11
Rick JamesRick James
43.8k22259
answered Apr 7 '17 at 18:11
Rick JamesRick James
43.8k22259
answered Apr 7 '17 at 18:11
Rick JamesRick James
43.8k22259
answered Apr 7 '17 at 18:11
Rick JamesRick James
43.8k22259
43.8k22259
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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.
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%2fdba.stackexchange.com%2fquestions%2f169429%2fmysql-query-to-fill-in-missing-datetime-values%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
The same problem answered here: stackoverflow.com/questions/24533485/…
– Tony H
Apr 7 '17 at 12:57