Ignition Report: Getting 1st day and last day of the month

I am working on ignition monthly report. I would like to use the same calender that I am using for start time and end time for 1 day and 3 day report.

The challenge that I am facing is with the scripting to get the 1st date of the month and last day of the month as end time to generate the monthly report. As an example from the calender drop down menu should I chose 7 October 2018, the report should automatically select Oct 1, 2018 as the start time and Oct 31, 2018 as end time.

thanks

1 Like

To get first day of month is easy with an expression:

dateFormat(dateArithmetic(now(0),0,'days'), "yyyy-MM-01 00:00:00")

Output would become: 2018-09-01 00:00:00

To get last day of month you need to script a little (to my knowledge), and this is what i got to return how many days in current month (28, 30, 31 etc):

import calendar

today = datetime.today()
year, month = today.year, today.month
calendar.monthrange(year, month)[1]

Output would become (for month 9): 30

2 Likes

First day of month:

getDate(
	getYear({date}),
	getMonth({date}),
	1
)

Last day of month:

addDays(
	getDate(
		getYear({date}),
		getMonth({date}) + 1,
		1
	),
	-1
)
15 Likes

@PGriffith’s approach is the best, as it will take care of all of the month lengths for you. Even better is to design your conditions and queries to use half-open intervals. That is, use the first of the month and the first of the following month as your boundaries, where the comparisons are “greater than or equal” and “less than”, respectively.

6 Likes

Thank you everyone for your help. I really appreciate it. I ended up suing PGriffith methodology as it was easier to implement. I have one note to make regarding “Last Day of Month” code as I had to make a slight modification. The code as it sits was not liking the +1 as it was expecting an integer. Below is the slight modification I made for the code to work.

addDays(getDate(getYear({date}),**toInt(**getMonth({date}) + **1),**1),-1)

3 Likes

Using scripting, what would be the best way to return a list of list containing first/last day of each month in a given year or consecutive years, given a year variable as the beginning year? Any guidance would be appreciated.

year = 2019
firstLastDay = []

Return:
[[Tue Jan 01 00:00:00 CST 2019, Thu Jan 31 00:00:00 CST 2019],
[Fri Feb 01 00:00:00 CST 2019, Thu Feb 28 00:00:00 CST 2019],
[Fri Mar 01 00:00:00 CST 2019, Sun Mar 31 00:00:00 CDT 2019],
[Mon Apr 01 00:00:00 CDT 2019, Tue Apr 30 00:00:00 CDT 2019],
[Wed May 01 00:00:00 CDT 2019, Fri May 31 00:00:00 CDT 2019],
[Sat Jun 01 00:00:00 CDT 2019, Sun Jun 30 00:00:00 CDT 2019],
[Mon Jul 01 00:00:00 CDT 2019, Wed Jul 31 00:00:00 CDT 2019],
[Thu Aug 01 00:00:00 CDT 2019, Sat Aug 31 00:00:00 CDT 2019], 
[Sun Sep 01 00:00:00 CDT 2019, Mon Sep 30 00:00:00 CDT 2019],
[Tue Oct 01 00:00:00 CDT 2019, Thu Oct 31 00:00:00 CDT 2019],
[Fri Nov 01 00:00:00 CDT 2019, Sat Nov 30 00:00:00 CST 2019],
[Sun Dec 01 00:00:00 CST 2019, Tue Dec 31 00:00:00 CST 2019], 
[Wed Jan 01 00:00:00 CST 2020, Fri Jan 31 00:00:00 CST 2020], 
[Sat Feb 01 00:00:00 CST 2020, Fri Feb 29 00:00:00 CST 2020],
[Sun Mar 01 00:00:00 CST 2020, Tue Mar 31 00:00:00 CDT 2020],
[Wed Apr 01 00:00:00 CDT 2020, Thu Apr 30 00:00:00 CDT 2020],
[Fri May 01 00:00:00 CDT 2020, Sun May 31 00:00:00 CDT 2020],
[Mon Jun 01 00:00:00 CDT 2020, Tue Jun 30 00:00:00 CDT 2020],
[Wed Jul 01 00:00:00 CDT 2020, Fri Jul 31 00:00:00 CDT 2020],
[Sat Aug 01 00:00:00 CDT 2020, Mon Aug 31 00:00:00 CDT 2020],
[Tue Sep 01 00:00:00 CDT 2020, Wed Sep 30 00:00:00 CDT 2020],
[Thu Oct 01 00:00:00 CDT 2020, Sat Oct 31 00:00:00 CDT 2020], 
[Sun Nov 01 00:00:00 CDT 2020, Mon Nov 30 00:00:00 CST 2020],
[Tue Dec 01 00:00:00 CST 2020, Thu Dec 31 00:00:00 CST 2020]]

The same principle applies:

def getMonths(year):
    date = system.date.getDate(year, 0, 0)
    dates = []

    for i in range(12):
    	date = system.date.addDays(date, 1)
        dates.append([date])
        date = system.date.addMonths(date, 1)
        date = system.date.addDays(date, -1)
        dates[i].append(date)
        
    return dates

for pair in getMonths(2019):
	print pair
>>> 
[Tue Jan 01 00:00:00 PST 2019, Thu Jan 31 00:00:00 PST 2019]
[Fri Feb 01 00:00:00 PST 2019, Thu Feb 28 00:00:00 PST 2019]
[Fri Mar 01 00:00:00 PST 2019, Sun Mar 31 00:00:00 PDT 2019]
[Mon Apr 01 00:00:00 PDT 2019, Tue Apr 30 00:00:00 PDT 2019]
[Wed May 01 00:00:00 PDT 2019, Fri May 31 00:00:00 PDT 2019]
[Sat Jun 01 00:00:00 PDT 2019, Sun Jun 30 00:00:00 PDT 2019]
[Mon Jul 01 00:00:00 PDT 2019, Wed Jul 31 00:00:00 PDT 2019]
[Thu Aug 01 00:00:00 PDT 2019, Sat Aug 31 00:00:00 PDT 2019]
[Sun Sep 01 00:00:00 PDT 2019, Mon Sep 30 00:00:00 PDT 2019]
[Tue Oct 01 00:00:00 PDT 2019, Thu Oct 31 00:00:00 PDT 2019]
[Fri Nov 01 00:00:00 PDT 2019, Sat Nov 30 00:00:00 PST 2019]
[Sun Dec 01 00:00:00 PST 2019, Tue Dec 31 00:00:00 PST 2019]
1 Like

When constructing this kind of list with the intention of using them to filter or manipulte events, consider using half-open intervals instead. That is, construct a simple list of dates, including the date just past the end of the period of interest. In this case, the list would contain all of the first days of the month. Then your “pairs” are the adjacent entries. This sets up comparisons in your following operations of the form

SELECT ...... WHERE t_stamp >= :startts AND t_stamp < :finishts

If you construct your list with an Ignition script, you’d first truncate a date to the first of the month, then the remaining entries would be formed by just using system.date.addMonths().

In SQL, you can construct such sequences with recursive Common Table Expressions and the DATEADD() function.

2 Likes

image

i am also trying same kind of thing

i have spinner if user select the date(day,month,year) . i want it to add to list. if user select multiple time i have to add it to list in same row

for example like this if user select 3 times

[Tue Jan 01 00:00:00 PST 2019, Thu Jan 31 00:00:00 PST 2019,Tue Jan 01 00:00:00 PST 2019]

how to achieve it?

my script

data = []
e = event.source.parent.getComponent('Spinner').dateValue
data.append(e)
print data

output

image

but i want it in single list.

Let’s put a few comments in your code.

# Create a new list
data = []
# Get spinner date 
e = event.source.parent.getComponent('Spinner').dateValue
# Add to the list
data.append(e)
# Print the list
print data

Every time the script runs, a new list is created. You will need to store that data to something, perhaps a custom property, after the new date is added.

Every time the scripts runs i have to use different custom property for each one?

i am trying to get output like this

['Thu May 06 00:00:00 IST 202' , 'FRI May 06 00:00:00 IST 202' , 'SAT May 06 00:00:00 IST 202']

Is there any better way to achieve what i am trying ?

I would add a table to the window so the user can visually keep track of the dates picked. Read the table data, read the dateValue, add a row to the table’s dataset.

ya but i am trying is different

selected = [‘Thu May 06 00:00:00 IST 202’ , ‘FRI May 06 00:00:00 IST 202’ , ‘SAT May 06 00:00:00 IST 202’]

todays date = strftime("%a %b %d 00:00:00 IST %Y ")

if todays date in selected :
excute somthing…

i am trying to compare. if dates match i will send mail or it won’t send mail

It’s not really any different, just because you want to compare the dates later. Get your dates stored first, then move to the next task.

If you want the dates to be permanent, store them to a dataset tag or, better yet, a database table.

1 Like

Thanks, that help a lot!

Here is how I find the first day and last day of a sample day by using Scripting.
The solution is find the first day of this month, then add 1 month to find the first day of next month, then minus 1 day to find the last day of this month

# Sample day
sampleDay = system.date.getDate(2024,5,14)
# Find the first day of this month
firstDayOfThisMonth = system.date.getDate(system.date.getYear(sampleDay), system.date.getMonth(sampleDay), 1)
# Find the first day of next month
firstDayOfNextMonth = system.date.addMonths(firstDayOfThisMonth, 1)
# Find the last day of this month
lastDayOfThisMonth = system.date.addDays(firstDayOfNextMonth , -1)
3 Likes