Weekly,monthly and year to date reporting

I know in the reporting module I can schedule reports to generate daily, weekly monthly, but I need to qualify the start time and date and end time and date before running a query against a database holding production run data.
i need to generate these reports on demand, ie, press a button for monthly report and i need to pass through scripting the first second of the month as the start and the last second of the month as the end to the query.

the only way I can figure is using now() and then applying date math to calculate the first second , issue will be last second. I guess i will need some sort of lookup table to figure if month has 28,29,30 or 31 days.

Other than date arithmetic is there an easier way?

i am trying to avoid having operator input select the first second and last second of the month

This is what we use to set the date range for a month on one of our windows.

from java.util import Calendar
now = Calendar.getInstance()
tYear = now.get(Calendar.YEAR)
tMon = now.get(Calendar.MONTH)+1
tDay = now.get(Calendar.DAY_OF_MONTH)
tHour = now.get(Calendar.HOUR)
tMin = now.get(Calendar.MINUTE)
tSec = now.get(Calendar.SECOND)
tYday = now.get(Calendar.DAY_OF_YEAR)
import calendar
lDay = calendar.monthrange(tYear, tMon)[1]

firstday= str(tMon)+'/1/'+str(tYear)+' 00:00:00'
lastday = str(tMon)+'/'+str(lDay)+'/'+str(tYear)+' 23:59:59'
from java.text import SimpleDateFormat
inputFormat=SimpleDateFormat("MM/dd/yyyy HH:mm:ss")  # Must match the format of the input string
dateStart=SimpleDateFormat.parse(inputFormat,firstday)
dateEnd=SimpleDateFormat.parse(inputFormat,lastday)
event.source.parent.parent.getComponent('Date Range').startDate=dateStart
event.source.parent.parent.getComponent('Date Range').endDate=dateEnd
1 Like

Since the parameters in 7.8 reporting use expressions, scripting is of limited help here.

You don’t have to maintain a table, but the expression gets a little ugly. Here’s an expression for the start of the current month: todate(dateformat(now(0), "YYYY-MM") + "-01 00:00:01") and the end of the current month dateArithmetic(dateArithmetic(todate(dateformat(now(0), "YYYY-MM") + "-01 23:59:59"),1,"month"),-1,"day") (Note: these work in a US locale – you may need to change the order of things in the date format expression elsewhere)

I put in a feature request for more date expressions, similar to the new system.date.* scripting functions that were added in 7.8. One way to get that to happen faster would be to also suggest it at ideas.inductiveautomation.com

Kathy,
This function for some reason has started returning the year as next year?
If I run it right now on a new 7.9.5 install it returns 12/1/2018 00:00:01
This is throwing our scheduled automatic monthly reports way off.

Try using yyyy-MM instead of YYYY-MM

Good point. That ‘y’ vs ‘Y’ always gets me. :facepalm:

For those who want the date formatting docs: https://docs.oracle.com/javase/7/docs/api/java/text/SimpleDateFormat.html