Good everyone, Someone for the doubts can guide me on how to use the node "date picker" and mysql or if you have any example already made to guide me from that, is that I need to do a data filtering from a date "x" to a date "y". Thank you very much.
This thread describes converting the date picker output format.
I adapted it to convert the date to YYYY-MM-HH and constructed this select statement which works for me:
select * from socketevent where ontime between "2022-06-02" and "22-06-05";
+------+---------------------+--------+---------------------+--------+------+
| id | ontime | onkwh | offtime | offkwh | temp |
+------+---------------------+--------+---------------------+--------+------+
| 5445 | 2022-06-02 06:56:52 | 31.12 | 2022-06-02 07:26:52 | 31.302 | NULL |
| 5446 | 2022-06-02 08:36:55 | 31.302 | 2022-06-02 08:43:08 | 31.396 | NULL |
| 5447 | 2022-06-02 13:55:50 | 31.397 | 2022-06-02 14:08:09 | 31.528 | NULL |
| 5448 | 2022-06-03 07:38:25 | 31.528 | 2022-06-03 08:08:26 | 31.688 | NULL |
| 5449 | 2022-06-03 08:08:34 | 31.69 | 2022-06-03 08:20:18 | 31.737 | NULL |
| 5450 | 2022-06-04 07:48:50 | 31.737 | 2022-06-04 08:18:50 | 31.934 | NULL |
| 5451 | 2022-06-04 08:35:52 | 31.935 | 2022-06-04 09:05:52 | 32.048 | NULL |
Note that "2022-06-02" is interpreted by the database as "2022-06-02 00:00" so records from 2nd June are retrieved, but no records from 5th June are.
Is your 2nd date (date to) formatted correctly? ("22-06-05" vs "2022-06-05")
Also, if your date format is correct, wouldn't "22-06-05" be interpreted as 2022-06-05 00:00:00? So nothing > that date/time would be selected (effectively leaving out all data for 2022-06-05)
Exactly so.
And I made a mistake with my second date, should indeed have used 4 digits for the year. Thanks to everyone who pointed this out!
Below are two pieces of code that use the date picker and create a mySQL query to select data within a date range:
- Setting the date range to a flow variable (you don't need to do this but since I use the date for different flows, I copy them to a flow variable)
[{"id":"8c60682c.a7a6d","type":"function","z":"3fad0f28.757898","g":"42813315.c49ccc","name":"Preset date range","func":"//Fix number of miliseconds in a day\nvar millisInDays = 86400000;\n\n//Create 2nd output message\nmsg1={}\nmsg1.payload = []\n\nmsg2={}\nmsg2.payload = []\n\n//Calculate from and to dates\n\nvar toDate = new Date()\nvar curDate = toDate.getDate()\nvar fromDate = new Date()\n\n//Set from date as 1st of the current month\nfromDate = fromDate - ((curDate-1)* millisInDays)\n\nmsg1.payload = fromDate;\nmsg2.payload = toDate;\n\nreturn [msg1,msg2];","outputs":2,"noerr":0,"initialize":"","finalize":"","libs":[],"x":892.6666564941406,"y":2704,"wires":[["a294dc9b.224eb8"],["633f373e.17de7"]]},{"id":"a294dc9b.224eb8","type":"ui_date_picker","z":"3fad0f28.757898","g":"42813315.c49ccc","name":"Date From","label":"Date From:","group":"67a39dee.c646e4","order":1,"width":"5","height":1,"passthru":true,"topic":"","topicType":"str","className":"","x":1092.6667175292969,"y":2655,"wires":[["701acdd3.436f34"]],"inputLabels":["Today"]},{"id":"633f373e.17de7","type":"ui_date_picker","z":"3fad0f28.757898","g":"42813315.c49ccc","name":"Date To","label":"Date To:","group":"67a39dee.c646e4","order":2,"width":"5","height":1,"passthru":true,"topic":"","topicType":"str","className":"","x":1092.3334045410156,"y":2751.3333740234375,"wires":[["181040cb.4583df"]],"inputLabels":["Today"]},{"id":"701acdd3.436f34","type":"moment","z":"3fad0f28.757898","g":"42813315.c49ccc","name":"Format from date","topic":"","input":"","inputType":"msg","inTz":"ETC/GMT","adjAmount":0,"adjType":"days","adjDir":"add","format":"YYYY-MM-DD","locale":"en_US","output":"fromDate","outputType":"flow","outTz":"America/Chicago","x":1299.3336486816406,"y":2654.3333740234375,"wires":[[]]},{"id":"181040cb.4583df","type":"moment","z":"3fad0f28.757898","g":"42813315.c49ccc","name":"Format to date","topic":"","input":"","inputType":"msg","inTz":"ETC/GMT","adjAmount":0,"adjType":"days","adjDir":"add","format":"YYYY-MM-DD","locale":"en_US","output":"toDate","outputType":"flow","outTz":"America/Chicago","x":1290.3335266113281,"y":2750.3333740234375,"wires":[[]]},{"id":"67a39dee.c646e4","type":"ui_group","name":"Date Range","tab":"660a703d.ea902","order":2,"disp":true,"width":"26","collapse":false},{"id":"660a703d.ea902","type":"ui_tab","name":"Tesla Solar Summary","icon":"fa-bar-chart","order":4,"disabled":false,"hidden":false}]
The flow uses the "node-red-contrib-moment" node to format the dates.
- Build the SQL query using the dates. I am including the whole SQL query, but the relevant section is in the last 2 lines in the function node that build the query:
sqlString += "date (created) >= '"+flow.get("fromDate")+"' "
sqlString += "and date (created) <= '"+flow.get("toDate")+"' "
the mySQL DATE() function returns the date in YYYY-MM-DD format, so the time is ignored and only the date records are matched.
//Build SQL query
var sqlString = ""
sqlString += "select round((load_home_imp_per - load_home_exp_per)/1000,2) as 'totalHome', "
sqlString += "round((site_grid_imp_per)/1000,2) as 'fromGrid', "
sqlString += "(round((solar_exp_per - solar_imp_per)/1000,2)-round((site_grid_exp_per)/1000,2)-round(battery_pwl_imp_per/1000,2)) as 'fromSolar', "
sqlString += "round(battery_pwl_exp_per/1000,2) as 'fromBattery', "
sqlString += "round((solar_exp_per - solar_imp_per)/1000,2) as 'totalSolar', "
sqlString += "(select fromSolar) as 'toHome', "
sqlString += "round((site_grid_exp_per)/1000,2) as 'toGrid', "
sqlString += "round(battery_pwl_imp_per/1000,2) as 'toBattery', "
sqlString += "round(((select fromGrid)/(select totalHome))*100,2) as 'fromGridPct', "
sqlString += "round(((select fromSolar)/(select totalHome))*100,2) as 'fromSolarPct', "
sqlString += "round(((select fromBattery)/(select totalHome))*100,2) as 'fromBatteryPct', "
sqlString += "round(((select toHome)/(select totalSolar))*100,2) as 'toHomePct', "
sqlString += "round(((select toBattery)/(select totalSolar))*100,2) as 'toBatteryPct', "
sqlString += "round(((select toGrid)/(select totalSolar))*100,2) as 'toGridPct', "
sqlString += "100 as 'totalHomePct', "
sqlString += "100 as 'totalSolarPct', "
sqlString += "date_format(created,'%b-%e') as 'labels', "
sqlString += "date(created)as 'created' "
sqlString += "from powerwall_cumm where "
sqlString += "date (created) >= '"+flow.get("fromDate")+"' "
sqlString += "and date (created) <= '"+flow.get("toDate")+"' "
msg.topic = sqlString;
return msg;
The output of the function node (the query MUST be in msg.topic) is sent to the mySQL node that executes the query and returns the results.
Hope this helps.
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.
