Select / EXTRACT query

General
1

Hello,
I save my gas meter "gasmeter" readings together with time and date "time" to my MariaDB "gas".
With the following query code:

Select
    EXTRACT(DAY FROM time),
    MAX(gasmeter)-MIN(gasmeter) AS "gasmeter"
From gas
WHERE
    MONTH(time) = {{payload}}
Group By CAST(time AS DATE);

in a template node I'm able to retrieve my data as daily consumption in mm² for one month and use it on my dashboard. "{{payload}}" comes from a dropdown node (1 through 12) for each month.
Is there a way to change the "MONTH(time) = {{payload}}" condition into a "YEAR_MONTH" condition, so I can use my saved data for longer than 12 months?
Thank you in advance.

2

You will need to restructure your table to hold yr_month as a string and you will need to populate the ur_month field from time when you write the record.

3

It isnt clear how you save the date time .. does the date include the year ?
can you show us a few records of the db ?

if you do have a year in the date ..

WHERE
    MONTH(time) = {{payload}} AND YEAR(time) = '2021'
4

A possibility is to define a view

CREATE VIEW daily_consumption AS SELECT
    EXTRACT(YEARMONTH FROM time) as yyyymm , EXTRACT(DAY FROM time) as dd,
    MAX(gasmeter)-MIN(gasmeter) AS "gasmeter"
FROM gas
GROUP BY EXTRACT(YEARMONTH FROM time), EXTRACT(DAY FROM time);

And pseudo node-red topic to retrieve the data for your chosen day:
select * from daily_consumption where yyyymm = msg.yyyymm and dd = msg.dd

5

Wow, thanks for your quick answers. I'm going to have lots of fun trying them out.
@UnborN: I'd thought of "WHERE / AND" but I couldn't find any code to separate the dropdown payload into month and year. This is what my db looks like:

ID     time                      gasmeter
3369   2020-11-01 06:01:21       739.720
3370   2020-11-01 06:03:23       739.725

Thank you all again
Jules

6

a "dropdown" ? using the dropdown ui node ?
why not use the Date picker ui node and then a Change node to split the date to year, month, day

Example:

[{"id":"20027bd84048d7dc","type":"ui_date_picker","z":"5847b7aa62131d37","name":"","label":"date","group":"6efcc19883dcdf68","order":1,"width":0,"height":0,"passthru":true,"topic":"topic","topicType":"msg","className":"","x":570,"y":900,"wires":[["029c8a24653d5328"]]},{"id":"64a1d77a9902faa9","type":"debug","z":"5847b7aa62131d37","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":990,"y":900,"wires":[]},{"id":"029c8a24653d5328","type":"change","z":"5847b7aa62131d37","name":"","rules":[{"t":"set","p":"payload","pt":"msg","to":"{\t    \"year\": $moment(msg.payload).format(\"YYYY\"),\t    \"month\": $moment(msg.payload).format(\"MM\"),\t    \"day\": $moment(msg.payload).format(\"DD\")\t}","tot":"jsonata"}],"action":"","property":"","from":"","to":"","reg":false,"x":760,"y":900,"wires":[["64a1d77a9902faa9"]]},{"id":"6efcc19883dcdf68","type":"ui_group","name":"Chart JS","tab":"39a6d442788cfb84","order":1,"disp":false,"width":"22","collapse":false,"className":"mychartgroup"},{"id":"39a6d442788cfb84","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

image

7

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.