英文:
MySQL query - Date Range
问题
抱歉，我无法满足您的要求，因为您要求不提供翻译。如果您有其他问题或需要帮助，请随时告诉我。
英文:
Hi I'm using the MySQL query below along side Grafana, and data relating to my electricity usage. The data collected ranges from late 2021 to current date, however the result only ranges from December 21 to November 22.
SELECT
Time AS "time",
SUM(Rate/800) as kWh
FROM Log
GROUP BY MONTH(STR_TO_DATE(time, "%Y-%m-%d"))
Can someone kindly assist with amending the query to display all relevant months from 2021 to 2023 and so on please.
答案1
得分: 1
请添加表结构和一些示例数据到你的问题中。
你当前的查询是非确定性的，如果启用了ONLY_FULL_GROUP_BY，将会出现1055错误。你可以在这里阅读更多关于MySQL对GROUP BY的处理。
你正在按MONTH(STR_TO_DATE(time, "%Y-%m-%d"))进行分组，这将对2021-01-02，2022-01-03和2023-01-04返回1。你需要按年和月分组。
如果你运行你查询的修改版本，你应该能够看到发生了什么：
SELECT
MONTH(STR_TO_DATE(`Time`, '%Y-%m-%d')) AS `m`,
MIN(`Time`) AS `min_time`,
MAX(`Time`) AS `max_time`,
SUM(`Rate`/800) AS `kWh`
FROM `Log`
GROUP BY MONTH(STR_TO_DATE(`Time`, '%Y-%m-%d'));
你对STR_TO_DATE()的使用似乎是不必要的，因为你正在使用标准的日期格式%Y-%m-%d。
SELECT
DATE_FORMAT(`Time`, '%Y-%m-01 00:00:00') AS `time`,
SUM(`Rate`/800) AS `kWh`
FROM `Log`
GROUP BY `time`
使用日期格式'%Y-%m'，2021-01-02，2022-01-03和2023-01-04将分别返回2021-01，2022-01和2023-01，并正确分组到相应的月份。
英文:
Please add your table structure and some sample data to your question.
Your current query is non-deterministic, and if you had ONLY_FULL_GROUP_BY enabled, would be failing with a 1055 error. You can read more about MySQL Handling of GROUP BY here.
You are grouping by MONTH(STR_TO_DATE(time, "%Y-%m-%d")) which will return 1 for 2021-01-02, 2022-01-03 and 2023-01-04. You need to group by both the year and month.
If you run this modified version of your query you should be able to see what is happening:
SELECT
MONTH(STR_TO_DATE(`Time`, '%Y-%m-%d')) AS `m`,
MIN(`Time`) AS `min_time`,
MAX(`Time`) AS `max_time`,
SUM(`Rate`/800) AS `kWh`
FROM `Log`
GROUP BY MONTH(STR_TO_DATE(`Time`, '%Y-%m-%d'));
Your use of STR_TO_DATE() appears unnecessary as you are using the standard date format %Y-%m-%d.
SELECT
DATE_FORMAT(`Time`, '%Y-%m-01 00:00:00') AS `time`,
SUM(`Rate`/800) AS `kWh`
FROM `Log`
GROUP BY `time`
With the date format '%Y-%m', 2021-01-02, 2022-01-03 and 2023-01-04 will be returned as 2021-01, 2022-01 and 2023-01 respectively, and grouped in the correct month.
答案2
得分: 0
尝试这个：
SELECT
Concat(YEAR(STR_TO_DATE(Time, '%Y-%m-%d')),'-', MONTH(STR_TO_DATE(Time, '%Y-%m-%d'))) as 月份,
SUM(Rate/800) as 千瓦时
FROM Log
GROUP BY YEAR(STR_TO_DATE(Time, '%Y-%m-%d')), MONTH(STR_TO_DATE(Time, '%Y-%m-%d'))
英文:
Try this :
SELECT
Concat(YEAR(STR_TO_DATE(Time, "%Y-%m-%d")),'-', MONTH(STR_TO_DATE(Time, "%Y-%m-%d"))) as month,
SUM(Rate/800) as kWh
FROM Log
GROUP BY YEAR(STR_TO_DATE(Time, "%Y-%m-%d")), MONTH(STR_TO_DATE(Time, "%Y-%m-%d"))
答案3
得分: 0
你可以使用以下查询来获得你想要的输出：
SELECT
DATE_FORMAT(STR_TO_DATE(Time, "%Y-%m-%d"), "%Y-%m") AS "Month",
SUM(Rate / 800) as kWh
FROM
Log
GROUP BY
DATE_FORMAT(STR_TO_DATE(Time, "%Y-%m-%d"), "%Y-%m"), Time
ORDER BY
STR_TO_DATE(Time, "%Y-%m-%d");
结果如下：
| Month | kWh |
|---|---|
| 2022-01 | 1700773808 |
| 2022-02 | 1770840497 |
| 2022-04 | 1741656455 |
| 2022-05 | 1738120832 |
| 2023-01 | 1752779938 |
英文:
You can use this query to get your desired output :
SELECT
DATE_FORMAT(STR_TO_DATE(Time, "%Y-%m-%d"),"%Y-%m") AS "Month",
SUM(Rate / 800) as kWh
FROM
Log
GROUP BY
DATE_FORMAT(STR_TO_DATE(Time, "%Y-%m-%d"),"%Y-%m"), Time
ORDER BY
STR_TO_DATE(Time, "%Y-%m-%d");
Results looks like :
| Month | kWh |
|---|---|
| 2022-01 | 1700773808 |
| 2022-02 | 1770840497 |
| 2022-04 | 1741656455 |
| 2022-05 | 1738120832 |
| 2023-01 | 1752779938 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论