# Format as Date for Dataset

**URL:** <https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076>\
**Category:** Ignition\
**Tags:** ignition79, scripting\
**Created:** [February 21, 2018, 6:08pm UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076 "2018-02-21T18:08:32Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![Stuart](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Stuart](https://forum.inductiveautomation.com/u/Stuart)\
**Post date:** [February 21, 2018, 6:08pm UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/1 "2018-02-21T18:08:32Z")

</div>

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):

```auto
"#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](https://docs.inductiveautomation.com/display/DOC79/Datasets#Datasets-CreatingDatasets), I am trying to make a small modification to these events via scripting.

```auto
# 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.

---

<div class="post-metadata">

**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\
**Post date:** [February 21, 2018, 6:27pm UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/2 "2018-02-21T18:27:53Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![Stuart](https://avatars.discourse-cdn.com/v4/letter/s/cab0a1/32.png) [@Stuart](https://forum.inductiveautomation.com/u/Stuart)\
**Post date:** [February 21, 2018, 6:33pm UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/3 "2018-02-21T18:33:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [February 21, 2018, 8:50pm UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/4 "2018-02-21T20:50:38Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bfuson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bfuson/32/4597_2.png) [@bfuson](https://forum.inductiveautomation.com/u/bfuson)\
**Post date:** [February 21, 2018, 11:15pm UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/5 "2018-02-21T23:15:05Z")

</div>

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:

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

```

Thanks!

---

<div class="post-metadata">

**Author:** ![KathyApplebaum](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kathyapplebaum/32/7261_2.png) [@KathyApplebaum](https://forum.inductiveautomation.com/u/KathyApplebaum)\
**Post date:** [February 21, 2018, 11:37pm UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/6 "2018-02-21T23:37:31Z")

</div>

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. 🙂

---

<div class="post-metadata">

**Author:** ![Sanderd17](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/sanderd17/32/8735_2.png) [@Sanderd17](https://forum.inductiveautomation.com/u/Sanderd17)\
**Post date:** [February 22, 2018, 10:04am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/7 "2018-02-22T10:04:05Z")

</div>

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__ `

```auto
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:

```auto
<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.

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [September 6, 2022, 8:30am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/8 "2022-09-06T08:30:37Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/e/4/e40e5a33317db9450d9990e75177fbfaa85b2715.png)  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/9/6/96217206a88a35c35c762ecf08e483aadaead219.png)

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [September 7, 2022, 7:58am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/9 "2022-09-07T07:58:34Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [September 7, 2022, 8:42am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/10 "2022-09-07T08:42:47Z")

</div>

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](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/1/613c8c599a3a83899020a4a8e65bbfbaa866aaf3.png)  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/a/5/a5392011d7c1212f9e057aeca2d043c8787a23d7.png)

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [September 7, 2022, 9:20am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/11 "2022-09-07T09:20:08Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [September 7, 2022, 9:41am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/12 "2022-09-07T09:41:57Z")

</div>

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

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/c/b/cb8bad17acaf149ad699f45be31c85b1707e5a38.png)

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [September 7, 2022, 9:58am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/13 "2022-09-07T09:58:53Z")

</div>

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

```auto
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

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [September 7, 2022, 10:05am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/14 "2022-09-07T10:05:53Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [September 7, 2022, 10:08am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/15 "2022-09-07T10:08:24Z")

</div>

> [@justinedwards.jle](#):
>
> %c/%e/%y, %k:%i:%s %p

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/f/7/f787eb2ec9e898ef794327ba7fb29be8c4337fe7.png)  
 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/b/0/b0e58804b605dd6b24b527c60938e5a426035ba3.png)

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [September 7, 2022, 10:12am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/16 "2022-09-07T10:12:38Z")

</div>

When you click okay, nothing happens?

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [September 7, 2022, 10:41am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/17 "2022-09-07T10:41:58Z")

</div>

No, just is like this. No errors…

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [September 7, 2022, 10:59am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/18 "2022-09-07T10:59:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![OscarLucas](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/oscarlucas/32/6417_2.png) [@OscarLucas](https://forum.inductiveautomation.com/u/OscarLucas)\
**Post date:** [September 7, 2022, 11:53am UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/19 "2022-09-07T11:53:33Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![justinedwards.jle](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/justinedwards.jle/32/37854_2.png) [@justinedwards.jle](https://forum.inductiveautomation.com/u/justinedwards.jle)\
**Post date:** [September 7, 2022, 12:16pm UTC](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076/20 "2022-09-07T12:16:41Z")

</div>

Here’s mine:

 ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/6/f/6fb4036fcede151904b4fbf40d4074527f3123a3.png)

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

[Next page](https://forum.inductiveautomation.com/t/format-as-date-for-dataset/17076.md?page=2)