Calculate Last Date of a Month

[smanjunath211]

Hi,

I need to generate, for a mysql query 'last day of a given month'. currently i am picking up first day of the month from a date picker in dashboard, i also have another date picker currently to pick the last date. is there an easy way to derive the last day similar to EOMONTH() function in MSExcel?

[Steve-Mcl]

In a function...

const selectedDate = new Date(msg.payload); //assuming date picker date is in msg.payload
const month = selectedDate.getMonth();
const year = selectedDate.getFullYear();
//msg.payload = new Date(year, month, 0).getDate(); //returns 31 for this month
//msg.payload = new Date(year, month, 0); //returns date object for last day of month
return msg;

Or

const selectedDate = new Date(msg.payload); //assuming date picker date is in msg.payload
const month = selectedDate.getMonth();
const year = selectedDate.getFullYear();
msg.payload = daysInMonth(month+1, year);
return msg;

/** month 1 = Jan */
function daysInMonth(iMonth, iYear)
{
    return 32 - new Date(iYear, iMonth - 1, 32).getDate();
}

[bakman2]

There is a LAST_DAY(date) function available in mysql. For the dashboard you could just add a pulldown with the months.

[Steve-Mcl]

Ah I missed the key part there...

@smanjunath211

for a mysql query

Well spotted.

[smanjunath211]

Perfect!! it worked.

[smanjunath211]

Thanks for this, i could definitely make use of this elsewhere, if required out side of mysql query.

[smanjunath211]

@bakman2

you could just add a pulldown with the months

that's another great idea, i could use drop down of months rather than date, but datepicker gives me YEAR also, there must be some way to make a month-year picker, let me explore...

There is an option in month picker for year as well...... Just noticed.

Thanks for making my dashboard leaner....

[smanjunath211]

Hi, is there a way of limiting the period of selection in the picker ? my database has data only from September of 2021, so the picker should not go below Sep-2021 and also upper limit to current month.

[UnborN]

@smanjunath211

is there a way of limiting the period of selection in the picker ?

hello .. maybe you can create a datetimepicker in ui_template
using an external library that supports those options

Datetimepicker Examples

Example flow:

[{"id":"d4a06909c7c81b1b","type":"ui_template","z":"54efb553244c241f","group":"397fec83949c5c0b","name":"","order":0,"width":"6","height":"6","format":"<link rel=\"stylesheet\" href=\"https://pro.fontawesome.com/releases/v5.10.0/css/all.css\" integrity=\"sha384-AYmEC3Yw5cVb3ZcuHtOA93w35dYTsvhLPVnYs9eStHfGJvOvKxVfELGroGkvsg+p\" crossorigin=\"anonymous\"/>\n\n   <!-- Bootstrap is not required for the picker to work-->\n    <script\n      src=\"https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/js/bootstrap.min.js\"\n      integrity=\"sha384-cn7l7gDp0eyniUwwAZgrzD06kc/tftFf19TOAs2zVinnD/C7E91j9yyk5//jjpt/\"\n      crossorigin=\"anonymous\"\n    ></script>\n\n    <link\n      href=\"https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/css/bootstrap.min.css\"\n      rel=\"stylesheet\"\n      integrity=\"sha384-KyZXEAg3QhqLMpG8r+8fhAXLRk2vvoC2f3B09zVXn8CA5QIVfZOJ3BCsw2P0p/We\"\n      crossorigin=\"anonymous\"\n    />\n    <!-- end bootstrap-->\n\n\n<!-- Popperjs -->\n<script src=\"https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.3/dist/umd/popper.min.js\" integrity=\"sha384-eMNCOe7tC1doHpGoWe/6oMVemdAVTMs2xqW4mwXrXsW0L84Iytr2wi5v2QjrP/xp\" crossorigin=\"anonymous\"></script>\n\n<script src=\"https://cdn.jsdelivr.net/gh/Eonasdan/tempus-dominus@master/dist/js/tempus-dominus.js\"></script>\n\n<link href=\"https://cdn.jsdelivr.net/gh/Eonasdan/tempus-dominus@master/dist/css/tempus-dominus.css\" rel=\"stylesheet\" />\n\n<div\n     class='input-group'\n     id='datetimepicker1'\n     data-td-target-input='nearest'\n     data-td-target-toggle='nearest'\n >\n   <input\n     id='datetimepicker1Input'\n     type='text'\n     class='form-control'\n     data-td-target='#datetimepicker1'\n   />\n   <span\n     class='input-group-text'\n     data-td-target='#datetimepicker1'\n     data-td-toggle='datetimepicker'\n   >\n     <span class='fas fa-calendar'></span>\n   </span>\n </div>\n\n\n<script>\nvar theScope = scope\n\nsetTimeout(()=> {\n\n// define tomorrow to limit maxDate option\ntheScope.tomorrow = new Date();\ntheScope.tomorrow.setHours(23,59,59);\n\ntheScope.picker = new tempusDominus.TempusDominus(document.getElementById('datetimepicker1'), {\n   restrictions: {\n     maxDate : theScope.tomorrow,\n   },\n   display: {\n     buttons: {\n       today: true\n     }\n   }\n });\n\n\n }, 1000)\n\n// event handler to send msg to Node-red\n$('#datetimepicker1').on('hide.td', () => {\n  let selectedDate = theScope.picker.viewDate\n  theScope.send({payload: {selectedDate}} )\n  });\n\n \n\n</script>","storeOutMessages":false,"fwdInMessages":false,"resendOnRefresh":false,"templateScope":"local","className":"","x":660,"y":980,"wires":[["43feacb623f686dd"]]},{"id":"43feacb623f686dd","type":"debug","z":"54efb553244c241f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":830,"y":980,"wires":[]},{"id":"397fec83949c5c0b","type":"ui_group","name":"Default","tab":"a3a053c01a1ffc93","order":1,"disp":true,"width":"20","collapse":false,"className":""},{"id":"a3a053c01a1ffc93","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]

[smanjunath211]

Thanks for this, but this is beyond my comprehension . I will go through the examples and see if i can get it to work.

[UnborN]

I worked a bit more on the above example with options more suited for your needs.
by adding in the options of the datetimepicker to disable date and time and also limit it to todays date.
(also added a button to send the selected date)

[{"id":"d4a06909c7c81b1b","type":"ui_template","z":"54efb553244c241f","group":"397fec83949c5c0b","name":"","order":0,"width":"6","height":"6","format":"<link rel=\"stylesheet\" href=\"https://pro.fontawesome.com/releases/v5.10.0/css/all.css\"\n  integrity=\"sha384-AYmEC3Yw5cVb3ZcuHtOA93w35dYTsvhLPVnYs9eStHfGJvOvKxVfELGroGkvsg+p\" crossorigin=\"anonymous\" />\n\n<!-- Bootstrap is not required for the picker to work-->\n<script src=\"https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/js/bootstrap.min.js\"\n  integrity=\"sha384-cn7l7gDp0eyniUwwAZgrzD06kc/tftFf19TOAs2zVinnD/C7E91j9yyk5//jjpt/\" crossorigin=\"anonymous\"></script>\n\n<link href=\"https://cdn.jsdelivr.net/npm/bootstrap@5.1.0/dist/css/bootstrap.min.css\" rel=\"stylesheet\"\n  integrity=\"sha384-KyZXEAg3QhqLMpG8r+8fhAXLRk2vvoC2f3B09zVXn8CA5QIVfZOJ3BCsw2P0p/We\" crossorigin=\"anonymous\" />\n<!-- end bootstrap-->\n\n\n<!-- Popperjs -->\n<script src=\"https://cdn.jsdelivr.net/npm/@popperjs/core@2.9.3/dist/umd/popper.min.js\"\n  integrity=\"sha384-eMNCOe7tC1doHpGoWe/6oMVemdAVTMs2xqW4mwXrXsW0L84Iytr2wi5v2QjrP/xp\" crossorigin=\"anonymous\"></script>\n\n<script src=\"https://cdn.jsdelivr.net/gh/Eonasdan/tempus-dominus@master/dist/js/tempus-dominus.js\"></script>\n\n<link href=\"https://cdn.jsdelivr.net/gh/Eonasdan/tempus-dominus@master/dist/css/tempus-dominus.css\" rel=\"stylesheet\" />\n\n<div class='input-group' id='datetimepicker1' data-td-target-input='nearest' data-td-target-toggle='nearest'>\n  <input\n     id='datetimepicker1Input'\n     type='text'\n     class='form-control'\n     data-td-target='#datetimepicker1'\n   />\n  <span\n     class='input-group-text'\n     data-td-target='#datetimepicker1'\n     data-td-toggle='datetimepicker'\n   >\n     <span class='fas fa-calendar'></span>\n  </span>\n</div>\n\n<button class=\"btn btn-primary btn-sm mt-3\" ng-click=\"send({ payload: getDate() })\">Send Selected Date</button>    \n\n<script>\n  var theScope = scope\n\nsetTimeout(()=> {\n\n// define tomorrow to limit maxDate option\ntheScope.tomorrow = new Date();\ntheScope.tomorrow.setHours(23,59,59);\n\ntheScope.picker = new tempusDominus.TempusDominus(document.getElementById('datetimepicker1'), {\n  useCurrent: false, \n  defaultDate: new Date(), \n  restrictions: {\n     maxDate : theScope.tomorrow,\n  },\n  display: {\n    buttons: {\n       today: true\n    },\n    components: {\n       clock:false,\n       date: false,\n    }\n  },\n \n });\n\n\n }, 1000)\n\n// getDate function \n theScope.getDate = function() {\n   return { payload: theScope.picker.viewDate.toLocaleString() }\n }\n\n\n</script>","storeOutMessages":false,"fwdInMessages":false,"resendOnRefresh":true,"templateScope":"local","className":"","x":480,"y":840,"wires":[["43feacb623f686dd"]]},{"id":"43feacb623f686dd","type":"debug","z":"54efb553244c241f","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":670,"y":840,"wires":[]},{"id":"397fec83949c5c0b","type":"ui_group","name":"Default","tab":"a3a053c01a1ffc93","order":1,"disp":true,"width":"20","collapse":false,"className":""},{"id":"a3a053c01a1ffc93","type":"ui_tab","name":"Home","icon":"dashboard","disabled":false,"hidden":false}]