ISO8061 Date Time Format

Ignition
1

Dear All,

I have a dataset with just 2 columns: Date/TIme and Values.

The date/time, I found out, is in ISO format, like, "2024-03-20T23:00:00Z". This is not recognized by Ignition.

system.dataset.formatDates() threw an error.

I need to correct the date/time to Igntion accepted format. How can I do it?

I was thinking of:

  • creating a dataset with 2 columns and assigning headings
  • from each row of the old dataset, reading the date/time and converting, copying the value and adding these as a row in the new dataset.

Or are there any other better quicker ways? Thanks.

2

To be clear: You get dates as strings, and you want to convert them to actual datetime objects, right ?

Then you'll need to parse them with system.date.parse.
formatDates is there to take a datetime object as input, and output a string representing that datetime in a specific format.

Datetimes objects do not have formats. Only their string representations do.
But since, in order to see them, they have to be represented... they're represented with some format, that actually has nothing to do with the date itself.

so, something like

system.date.parse(d, "yyyy-MM-dd'T'HH:mm:ss'Z'")
1 Like
3

Also be aware that 'Z' indicates that the time is UTC or "Zero time zone". You may need to take this into account in your application and handle both time zone and daylight savings - if applicable.

2 Likes
4

You will need to do this as the original dataset is immutable.

java.time.Instant can take care of that:

sampleHeaders = ['t_stamp', 'value']
sampleData = [['2024-03-20T23:00:00Z', 5],
              ['2024-03-20T23:10:00Z', 4],
              ['2024-03-20T23:20:00Z', 3],
              ['2024-03-20T23:30:00Z', 2],
              ['2024-03-20T23:40:00Z', 1]
             ]

sampleDataSet = system.dataset.toDataSet(sampleHeaders, sampleData)

############################################################

from java.time import Instant
from java.util import Date

def zuluTimeToDate(dateIn):
	return system.date.fromMillis(Instant.parse(dateIn).toEpochMilli())
	
dataIn = sampleDataSet

headers = list(dataIn.columnNames)
data = []
for row in xrange(dataIn.rowCount):
	data.append([zuluTimeToDate(dataIn.getValueAt(row,0)), dataIn.getValueAt(row,1)])

dataOut = system.dataset.toDataSet(headers, data)

row | t_stamp                      | value
------------------------------------------
0   | Wed Mar 20 19:00:00 EDT 2024 | 5    
1   | Wed Mar 20 19:10:00 EDT 2024 | 4    
2   | Wed Mar 20 19:20:00 EDT 2024 | 3    
3   | Wed Mar 20 19:30:00 EDT 2024 | 2    
4   | Wed Mar 20 19:40:00 EDT 2024 | 1

EDIT:
java.text.SimpleDateFormat will work too.

sampleHeaders = ['t_stamp', 'value']
sampleData = [['2024-03-20T23:00:00Z', 5],
              ['2024-03-20T23:10:00Z', 4],
              ['2024-03-20T23:20:00Z', 3],
              ['2024-03-20T23:30:00Z', 2],
              ['2024-03-20T23:40:00Z', 1]
             ]

sampleDataSet = system.dataset.toDataSet(sampleHeaders, sampleData)

############################################################

from java.text import SimpleDateFormat

dataIn = sampleDataSet

dateFormat = SimpleDateFormat("yyyy-MM-dd'T'HH:mm:ssX")

headers = list(dataIn.columnNames)
data = []
for row in xrange(dataIn.rowCount):
	data.append([dateFormat.parse(dataIn.getValueAt(row,0)), dataIn.getValueAt(row,1)])

dataOut = system.dataset.toDataSet(headers, data)
2 Likes
5

What Jordan said, although I'd expect a pure-dataset approach to be a little faster than building a new dataset via Python objects:

Though the example script I have there doesn't support changing the column types. I'll leave that as an exercise for the reader :slight_smile:

6

Thanks. I will give it a try.

7

Thanks a lot Jordan! Much appreciated.

I will try these out one by one, will take me some time though. :smiley:

8

Agreed Transistor. Absolutely. We use DST, so gives that extra bit of work.

Are there any library/function that can handle this automatically? For example, takes the UTC time and desired time zone and return the converted time?

9

Ignition does it automatically of you let your database base save in UTC. In Perspective Designer you can specify whether the project should convert to gateway timezone or client timezone. I don't know how Vision handles it.

10

Thanks PG. I will give that a try as well. Cheers.

11

Dear All,

I have been able convert the date/time string into date/time type.

I took Jordan's solution and played around with it till I got what I wanted. Basically declared a new dataset and wrote on it row by row. Everything's sweet.

Now. let's move on to another hurdle. :smiley:

Thank you everyone. Hope you all have a lovely break!