Question 20898 in Mysql Question posted in
The official MySQL documentation can be found here.

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.

Tags:

3

Answers


  1. 0

    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"))
    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
  3. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search