# Select / EXTRACT query

**URL:** <https://discourse.nodered.org/t/select-extract-query/53218>\
**Category:** General\
**Tags:** database\
**Created:** [4 November 2021 14:57 UTC](https://discourse.nodered.org/t/select-extract-query/53218 "2021-11-04T14:57:15Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![jules](https://avatars.discourse-cdn.com/v4/letter/j/41988e/32.png) [@jules](https://discourse.nodered.org/u/jules)\
**Post date:** [4 November 2021 14:57 UTC](https://discourse.nodered.org/t/select-extract-query/53218/1 "2021-11-04T14:57:15Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![TotallyInformation](https://sea2.discourse-cdn.com/flex026/user_avatar/discourse.nodered.org/totallyinformation/32/31_2.png) [@TotallyInformation](https://discourse.nodered.org/u/TotallyInformation)\
**Post date:** [4 November 2021 16:36 UTC](https://discourse.nodered.org/t/select-extract-query/53218/2 "2021-11-04T16:36:20Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [4 November 2021 18:40 UTC](https://discourse.nodered.org/t/select-extract-query/53218/3 "2021-11-04T18:40:26Z")

</div>

> [@jules](#):
>
> I save my gas meter "gasmeter" readings together with time and date "time"

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 ..

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

```

---

<div class="post-metadata">

**Author:** ![jbudd](https://avatars.discourse-cdn.com/v4/letter/j/5f8ce5/32.png) [@jbudd](https://discourse.nodered.org/u/jbudd)\
**Post date:** [4 November 2021 21:11 UTC](https://discourse.nodered.org/t/select-extract-query/53218/4 "2021-11-04T21:11:31Z")

</div>

A possibility is to define a view

```auto
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`

---

<div class="post-metadata">

**Author:** ![jules](https://avatars.discourse-cdn.com/v4/letter/j/41988e/32.png) [@jules](https://discourse.nodered.org/u/jules)\
**Post date:** [5 November 2021 11:34 UTC](https://discourse.nodered.org/t/select-extract-query/53218/5 "2021-11-05T11:34:14Z")

</div>

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:

```auto
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

---

<div class="post-metadata">

**Author:** ![UnborN](https://avatars.discourse-cdn.com/v4/letter/u/4491bb/32.png) [@UnborN](https://discourse.nodered.org/u/UnborN)\
**Post date:** [5 November 2021 12:09 UTC](https://discourse.nodered.org/t/select-extract-query/53218/6 "2021-11-05T12:09:43Z")

</div>

> [@jules](#):
>
> I couldn't find any code to separate the dropdown payload into month and year.

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:**

```auto
[{"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](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/3X/e/c/ecaa29109f0b67b8c4ab4671986b70f7156c9129.png)

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/flex026/uploads/nodered/original/1X/d073cd938eafa2e558d7c2cd59003b3ef4963033.png) [@system](https://discourse.nodered.org/u/system)\
**Post date:** [4 January 2022 12:09 UTC](https://discourse.nodered.org/t/select-extract-query/53218/7 "2022-01-04T12:09:48Z")

</div>

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