Generate a sequential date_time by minute in MySQL
0
I'm using the below MySQL found on the web to generate a list of dates.
However, I want it to generate a list of date_times by minute, E.G yyyy-mm-dd hh:mm:ss.
Is this possible? I can't wrap my head around how the existing SQL generates the list nevermind adding minutes!
I need this for my base query for a chart / graph, using left join to pull in data from elsewhere.
Thanks
select * from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2019-01-01' and '2019-01-15'
Current output:
selected_date
---
2019-01-01
2019-01-02
2019-01-03
2019-01-04
Desired output:
selected_date
---
2019-01-19 00:05:00
2019-01-19 00:06:00
2019-01-19 00:07:00
2019-01-19 00:08:00
2019-01-19 00:09:00
2019-01-19 00:10:00
2019-01-19 00:11:00
Edit:
This is my SQL query serving the graph. I get 5-6 temperature records per minute, so I'm averaging them.
My problem is sometimes a temperature probe drops off, so a.date_time or b.date_time doesn't have a consecutive date_time for every single minute
select
b.date_time,
a.temperature as fishtank,
b.temperature as room
from (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
date_time between '$date_from' and '$date_to' and
serial like '28-000898430d59'
group by
date_time div 100
) a
right join (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
date_time between '$date_from' and '$date_to' and
serial like '28-000f9843201e'
group by
date_time div 100
) b on a.date_time = b.date_time
Edit again:
I've not managed to generate what I wanted, however I've come up with this which uses existing values in the DB to list dates. I think this will work for me
SELECT
a.date_time,
b.temperature as FishTank,
c.temperature as Room
FROM (
select
convert((min(date_time) div 100)*100, datetime) as date_time
from
raspicontroller.temperature
group by
date_time div 100
) as a
LEFT JOIN (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
serial like '28-000898430d59'
group by
date_time div 100
) as b on a.date_time = b.date_time
LEFT JOIN (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
serial like '28-000f9843201e'
group by
date_time div 100
) as c on a.date_time = c.date_time
mysql date datetime time
asked Jan 19 at 9:35
DanielDaniel
263
add a comment |
0
I'm using the below MySQL found on the web to generate a list of dates.
However, I want it to generate a list of date_times by minute, E.G yyyy-mm-dd hh:mm:ss.
Is this possible? I can't wrap my head around how the existing SQL generates the list nevermind adding minutes!
I need this for my base query for a chart / graph, using left join to pull in data from elsewhere.
Thanks
select * from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2019-01-01' and '2019-01-15'
Current output:
selected_date
---
2019-01-01
2019-01-02
2019-01-03
2019-01-04
Desired output:
selected_date
---
2019-01-19 00:05:00
2019-01-19 00:06:00
2019-01-19 00:07:00
2019-01-19 00:08:00
2019-01-19 00:09:00
2019-01-19 00:10:00
2019-01-19 00:11:00
Edit:
This is my SQL query serving the graph. I get 5-6 temperature records per minute, so I'm averaging them.
My problem is sometimes a temperature probe drops off, so a.date_time or b.date_time doesn't have a consecutive date_time for every single minute
select
b.date_time,
a.temperature as fishtank,
b.temperature as room
from (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
date_time between '$date_from' and '$date_to' and
serial like '28-000898430d59'
group by
date_time div 100
) a
right join (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
date_time between '$date_from' and '$date_to' and
serial like '28-000f9843201e'
group by
date_time div 100
) b on a.date_time = b.date_time
Edit again:
I've not managed to generate what I wanted, however I've come up with this which uses existing values in the DB to list dates. I think this will work for me
SELECT
a.date_time,
b.temperature as FishTank,
c.temperature as Room
FROM (
select
convert((min(date_time) div 100)*100, datetime) as date_time
from
raspicontroller.temperature
group by
date_time div 100
) as a
LEFT JOIN (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
serial like '28-000898430d59'
group by
date_time div 100
) as b on a.date_time = b.date_time
LEFT JOIN (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
serial like '28-000f9843201e'
group by
date_time div 100
) as c on a.date_time = c.date_time
mysql date datetime time
asked Jan 19 at 9:35
DanielDaniel
263
This is for a chart or graph, so you're using some kind of presentation layer. It's far better to handle the display logic there.
– Strawberry
Jan 19 at 9:42
I'm not sure what you mean. I'm running a SQL query, converting the output to a google datatable, then plotting the data in a google chart. I get temperature values every 5 seconds in my database, so I'm averaging the values every minute and performing a join. My issue, is sometimes I don't get a value for 10 minutes or so, which means my join doesn't function as expected
– Daniel
Jan 19 at 10:38
add a comment |
0
0
0
I'm using the below MySQL found on the web to generate a list of dates.
However, I want it to generate a list of date_times by minute, E.G yyyy-mm-dd hh:mm:ss.
Is this possible? I can't wrap my head around how the existing SQL generates the list nevermind adding minutes!
I need this for my base query for a chart / graph, using left join to pull in data from elsewhere.
Thanks
select * from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2019-01-01' and '2019-01-15'
Current output:
selected_date
---
2019-01-01
2019-01-02
2019-01-03
2019-01-04
Desired output:
selected_date
---
2019-01-19 00:05:00
2019-01-19 00:06:00
2019-01-19 00:07:00
2019-01-19 00:08:00
2019-01-19 00:09:00
2019-01-19 00:10:00
2019-01-19 00:11:00
Edit:
This is my SQL query serving the graph. I get 5-6 temperature records per minute, so I'm averaging them.
My problem is sometimes a temperature probe drops off, so a.date_time or b.date_time doesn't have a consecutive date_time for every single minute
select
b.date_time,
a.temperature as fishtank,
b.temperature as room
from (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
date_time between '$date_from' and '$date_to' and
serial like '28-000898430d59'
group by
date_time div 100
) a
right join (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
date_time between '$date_from' and '$date_to' and
serial like '28-000f9843201e'
group by
date_time div 100
) b on a.date_time = b.date_time
Edit again:
I've not managed to generate what I wanted, however I've come up with this which uses existing values in the DB to list dates. I think this will work for me
SELECT
a.date_time,
b.temperature as FishTank,
c.temperature as Room
FROM (
select
convert((min(date_time) div 100)*100, datetime) as date_time
from
raspicontroller.temperature
group by
date_time div 100
) as a
LEFT JOIN (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
serial like '28-000898430d59'
group by
date_time div 100
) as b on a.date_time = b.date_time
LEFT JOIN (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
serial like '28-000f9843201e'
group by
date_time div 100
) as c on a.date_time = c.date_time
mysql date datetime time
asked Jan 19 at 9:35
DanielDaniel
263
I'm using the below MySQL found on the web to generate a list of dates.
However, I want it to generate a list of date_times by minute, E.G yyyy-mm-dd hh:mm:ss.
Is this possible? I can't wrap my head around how the existing SQL generates the list nevermind adding minutes!
I need this for my base query for a chart / graph, using left join to pull in data from elsewhere.
Thanks
select * from
(select adddate('1970-01-01',t4.i*10000 + t3.i*1000 + t2.i*100 + t1.i*10 + t0.i) selected_date from
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t1,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3,
(select 0 i union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t4) v
where selected_date between '2019-01-01' and '2019-01-15'
Current output:
selected_date
---
2019-01-01
2019-01-02
2019-01-03
2019-01-04
Desired output:
selected_date
---
2019-01-19 00:05:00
2019-01-19 00:06:00
2019-01-19 00:07:00
2019-01-19 00:08:00
2019-01-19 00:09:00
2019-01-19 00:10:00
2019-01-19 00:11:00
Edit:
This is my SQL query serving the graph. I get 5-6 temperature records per minute, so I'm averaging them.
My problem is sometimes a temperature probe drops off, so a.date_time or b.date_time doesn't have a consecutive date_time for every single minute
select
b.date_time,
a.temperature as fishtank,
b.temperature as room
from (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
date_time between '$date_from' and '$date_to' and
serial like '28-000898430d59'
group by
date_time div 100
) a
right join (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
date_time between '$date_from' and '$date_to' and
serial like '28-000f9843201e'
group by
date_time div 100
) b on a.date_time = b.date_time
Edit again:
I've not managed to generate what I wanted, however I've come up with this which uses existing values in the DB to list dates. I think this will work for me
SELECT
a.date_time,
b.temperature as FishTank,
c.temperature as Room
FROM (
select
convert((min(date_time) div 100)*100, datetime) as date_time
from
raspicontroller.temperature
group by
date_time div 100
) as a
LEFT JOIN (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
serial like '28-000898430d59'
group by
date_time div 100
) as b on a.date_time = b.date_time
LEFT JOIN (
select
serial,
convert((min(date_time) div 100)*100, datetime) as date_time,
avg(temperature) as temperature
from
raspicontroller.temperature
WHERE
serial like '28-000f9843201e'
group by
date_time div 100
) as c on a.date_time = c.date_time
mysql date datetime time
mysql date datetime time
asked Jan 19 at 9:35
DanielDaniel
263
asked Jan 19 at 9:35
DanielDaniel
263
edited Jan 19 at 10:57
Daniel
asked Jan 19 at 9:35
DanielDaniel
263
asked Jan 19 at 9:35
DanielDaniel
263
asked Jan 19 at 9:35
DanielDaniel
263
263
This is for a chart or graph, so you're using some kind of presentation layer. It's far better to handle the display logic there.
– Strawberry
Jan 19 at 9:42
I'm not sure what you mean. I'm running a SQL query, converting the output to a google datatable, then plotting the data in a google chart. I get temperature values every 5 seconds in my database, so I'm averaging the values every minute and performing a join. My issue, is sometimes I don't get a value for 10 minutes or so, which means my join doesn't function as expected
– Daniel
Jan 19 at 10:38
add a comment |
This is for a chart or graph, so you're using some kind of presentation layer. It's far better to handle the display logic there.
– Strawberry
Jan 19 at 9:42
I'm not sure what you mean. I'm running a SQL query, converting the output to a google datatable, then plotting the data in a google chart. I get temperature values every 5 seconds in my database, so I'm averaging the values every minute and performing a join. My issue, is sometimes I don't get a value for 10 minutes or so, which means my join doesn't function as expected
– Daniel
Jan 19 at 10:38
This is for a chart or graph, so you're using some kind of presentation layer. It's far better to handle the display logic there.
– Strawberry
Jan 19 at 9:42
This is for a chart or graph, so you're using some kind of presentation layer. It's far better to handle the display logic there.
– Strawberry
Jan 19 at 9:42
I'm not sure what you mean. I'm running a SQL query, converting the output to a google datatable, then plotting the data in a google chart. I get temperature values every 5 seconds in my database, so I'm averaging the values every minute and performing a join. My issue, is sometimes I don't get a value for 10 minutes or so, which means my join doesn't function as expected
– Daniel
Jan 19 at 10:38
I'm not sure what you mean. I'm running a SQL query, converting the output to a google datatable, then plotting the data in a google chart. I get temperature values every 5 seconds in my database, so I'm averaging the values every minute and performing a join. My issue, is sometimes I don't get a value for 10 minutes or so, which means my join doesn't function as expected
– Daniel
Jan 19 at 10:38
add a comment |
0
active
oldest
votes
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%2f54265750%2fgenerate-a-sequential-date-time-by-minute-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
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%2f54265750%2fgenerate-a-sequential-date-time-by-minute-in-mysql%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
This is for a chart or graph, so you're using some kind of presentation layer. It's far better to handle the display logic there.
– Strawberry
Jan 19 at 9:42
I'm not sure what you mean. I'm running a SQL query, converting the output to a google datatable, then plotting the data in a google chart. I get temperature values every 5 seconds in my database, so I'm averaging the values every minute and performing a join. My issue, is sometimes I don't get a value for 10 minutes or so, which means my join doesn't function as expected
– Daniel
Jan 19 at 10:38