Format as Date for Dataset

Ignition
,
1

I have a Month View Calendar, and am trying to modify the dataset containing the events.

The default dataset for Month View is (when copy/pasted):

"#NAMES"
"startDate","endDate","displayColor","display"
"#TYPES"
"date","date","str","str"
"#ROWS","4"
"2018-02-21 08:00:35","2018-02-21 12:00:35","blue","Meeting"
"2018-02-21 08:00:35","2018-02-21 09:30:35","red","Email Customer"
"2018-02-21 13:00:35","2018-02-21 15:00:35","orange","Phone Call"
"2018-02-22 08:00:35","2018-02-22 17:00:35","blue","Another Meeting"

Using the example given on this page, I am trying to make a small modification to these events via scripting.

# First create a list that contains the headers, in this case there are 4 headers.
headers = ["startDate", "endDate", "displayColor", "display"]
 
# Then create an empty list, this will house our data.
data = []
 
# Then add each row to the list. Note that each row is also a list object.
data.append(["2018-02-21 08:00:35","2018-02-21 12:00:35","blue","First Event"])
data.append(["2018-02-20 08:00:28","2018-02-20 09:30:28","red","Second Event"])
data.append(["2018-02-20 13:00:28","2018-02-20 15:00:28","orange","Third Event"])
data.append(["2018-02-21 08:00:28","2018-02-21 17:00:28","blue","Fourth Event"])

 
# Finally, both the headers and data lists are used in the function to create a Dataset object
calendar = system.dataset.toDataSet(headers, data)

# Set the month view event property to the new dataset
event.source.parent.getComponent('Month View').events = calendar

However… When I run that sript, everything disappears off my calendar. I am assuming it has to do with the date being passed as a string rather than a date.

How can I format the string as a date?

I’ve tried to use the system.date.parse('2018-02-20 08:00:28','MMMM dd, yyyy hh:mm') function but
got errors: java.text.ParseException: java.text.ParseException: Unparseable date: "2018-02-20 08:00:28"

Any suggestions?

Eventually I will want these events to be populated from a database, but I was hoping to test my program before integrating the database.

1 Like
2

You need to change the format string that system.date.parse is using to fit the format of your string:

system.date.parse('2018-02-20 08:00:28','yyyy-MM-dd HH:mm:ss')
3

Thanks… That worked exactly how I expected mine to work. Solved my issue for now.

4

Sneak peek of things to come: Ignition 7.9.7 will have a new scripting function, system.dataset.formatDates() that will return a new dataset with all the dates formatted in the format (and optional locale) you specify.

3 Likes
5

That sounds fantastic! Most of my column-based dataset manipulations are to do precisely this. Is there any possibility of Ignition’s datasets getting column accessors/mutators like those in pandas or numpy?

Something similar to:

arr[:,0] = arr[:,0] - 1

Thanks!

6

Not sure how we could make that work inside Jython, but I know a lot of people would like that feature. Suggest it in the ideas forum and we may be able to figure out some magic. :slight_smile:

1 Like
7

Python is quite versatile with this. The only thing you have to do is define a __getitem__ function on the class, with the right handling

You can try this code to see what’s being passed to the __getitem__

class dummyDs(object):
	def __getitem__(self, index):
		print type(index)
		print index

ds = dummyDs()

ds[0]
ds[0:10]
ds[:]
ds[0,0]
ds[:,0]
ds[0,:]

These are the console results:

<type 'int'>
0
<type 'slice'>
slice(0, 10, None)
<type 'slice'>
slice(None, None, None)
<type 'tuple'>
(0, 0)
<type 'tuple'>
(slice(None, None, None), 0)
<type 'tuple'>
(0, slice(None, None, None))

So for a single numerical index, you just get the number as argument. For a slice, you get a slice object (with a start, end and step size). But more interesting, when you pass multiple indexes, you get a tuple back with the indexes or ranges.

2 Likes
8

I try to use this formats but do not work with any.
Can you help me?
@KathyApplebaum

image
image1843×921 175 KB

image
image1758×850 157 KB

9

@OscarLucas
Where are you running your script from, and what columns in the dataset are you wanting to manipulate?

10

i am using a named queries @justinedwards.jle

SELECT MIN(date_format(fichajes.inicio,‘%Y-%m-%d %k:%i:%s’)) as startDate,
MAX(date_format(fichajes.fin,‘%Y-%m-%d %k:%i:%s’))as endDate,
“blue” as displayColor,
QR as display
FROM fichajes
WHERE MONTH(fichajes.inicio) = :Mes AND YEAR(fichajes.inicio) = :Ano AND usuario = :Usuario
group by DAY(fichajes.inicio)

image
image1388×332 23.5 KB

image
image1828×933 209 KB

11

It looks like the date times are coming through to the dataset in alignment with the SQL query. What format are you looking for?

Example: 2022-September-06 21:09:19

Edit: or am I misunderstanding you question?

12

Well, my question is basically, what format do I have to put in for it to work? Because it does not work.

image
image1708×797 134 KB

13

Understood. It could be because you are missing the meridian, Try adding %p to you query:

SELECT MIN(date_format(fichajes.inicio,‘%c/%e/%y, %k:%i:%s %p)) as startDate,
MAX(date_format(fichajes.fin,‘%c/%e/%y, %k:%i:%s %p’))as endDate,
“blue” as displayColor,
QR as display
FROM fichajes
WHERE MONTH(fichajes.inicio) = :Mes AND YEAR(fichajes.inicio) = :Ano AND usuario = :Usuario
group by DAY(fichajes.inicio)

Edit: Changed format parameters for month, day, and year

14

Actually, I believe the correct format to be:
%c/%e/%y, %k:%i:%s %p

15

image
image1468×864 145 KB

image
image803×716 58.2 KB

16

When you click okay, nothing happens?

17

No, just is like this. No errors…

18

Sorry to hear that. It looks perfect now when compared to mine. Perhaps somebody else will have an idea.

19

Can you share yours? is there any other way to use it? @justinedwards.jle

20

Here’s mine:

image
image935×379 18.5 KB

I’m sure it’s going to be something obvious and simple, but at the moment, I’m just not seeing the problem.