Converting a dataset with system.dataset.toDataSet changes date to integer

[r123]

I'd like the date to be something like "2023-07-21T02:00:00Z" in the dataset.

At first I thought it needed to be converted like this but that caused errors like it already was.

import datetime
your_timestamp = 1690912200000
print your_timestamp
date = datetime.datetime.fromtimestamp(your_timestamp / 1e3)
print date

When I look at the value of timestamp_numeric before and after the tagDataset.append I get this 2023-08-01 12:59:00.000.

for row in range(historicalValues.getRowCount()):
    # Get data from each row in historicalValues
    timestamp_numeric = historicalValues.getValueAt(row, 0)  # Assuming the timestamp is in the first column
    system.perspective.print(timestamp_numeric)
    value = historicalValues.getValueAt(row, 1)  # Assuming the value is in the second column
    # Append data to tagDataset
    tagDataset.append([system_ParentFolder, sector_ParentFolder, unit_ParentFolder, browseTag.name, value, timestamp_numeric])
    system.perspective.print(timestamp_numeric)

When I look at the resulting dataset it's an integer. Is that all the dataset supports?
"timestamp":1690912200000

[r123]

Hmm... It was the system.dataset.toDataSet conversion after, that changed the format. Added code to make it a string first.

for row in range(historicalValues.getRowCount()):
    # Get data from each row in historicalValues
    timestamp = historicalValues.getValueAt(row, 0)  # Assuming the timestamp is in the first column
    value = historicalValues.getValueAt(row, 1)  # Assuming the value is in the second column
    
    # Convert the timestamp to a string representation
    timestamp_str = str(timestamp)
    
    # Append data to tagDataset
    #tagDataset.append([system_ParentFolder, sector_ParentFolder, unit_ParentFolder, browseTag.name, historicalValues.getValueAt(0,1), historicalValues.getValueAt(0,0)])
    tagDataset.append([system_ParentFolder, sector_ParentFolder, unit_ParentFolder, browseTag.name, value, timestamp_str])

MyDataset = system.dataset.toDataSet(tagHeaders, tagDataset)
self.parent.parent.getChild("Label_1").props.text = MyDataset 

[pascal.fragnoud]

My money is on representation. I'd bet nothing was converted, but the method used to display the dates made it show as different things. Can you show the whole script ?

Also, you don't need to import datetime, you can and should use system.date.* functions.

[pturmel]

@pascal.fragnoud

My money is on representation

Concur.

You are trying to dictate the generic string representation of a java.util.Date object in your dataset. No can do. (Though you might place java.sql.Timestamp objects in their place--subclasses of java.util.Date that have a generic string representation closer to what you want.)

Don't convert to string! (In the dataset. Convert to string at the point of display only.)

String conversions are the source of endless grief and frustration.

For UI display purposes, consider this:

Ignition Perspective Page to show the client System date and time

Sure. I recommend DateTimeFormatter.ofPattern(). But I would move the complexity to a project script module with a DateTimeFormatter instance with your preferred pattern saved as a top level variable. Like so, perhaps: from java.time import ZoneId from java.time.format import DateTimeFormatter defaultFormatter = DateTimeFormatter.ofPattern("YYYY-MM-dd HH:mm") def zonedFormat(ts, zoneid, format=None): formatter = defaultFormatter if format is None else DateTimeFormatter.ofPattern(format) r…

[r123]

Not sure what else to call it, system.dataset.toDataSet is used to convert datasets. To my limited understanding it also changed/converted the timestamp format. I need the dataset to be in what I think is JSON format and this seems to work, save for the timestamp format conversion. I was given an example of the JSON formatted data and was just trying to match everything. It shows the timestamp (ISO8601?) like this:
"timestamp": "2023-08-01T18:34:00Z"
Not like this:
"timestamp":1690912200000

I've been using self.parent.parent.getChild("Label_1").props.text = MyDataset to see what is in the datasets on a Perspective screen. Is there a better way?
I was doing more in different datasets and the date format was ok until using MyDataset = system.dataset.toDataSet(tagHeaders, tagDataset).

Is the print command or label object not really showing what is in the dataset? I was hoping it was just showing the text as is and not formatting it.

@pascal.fragnoud

Also, you don't need to import datetime, you can and should use system.date.* functions.

That was just the test I was using in the Script Console, copied from an example. I forget now but I was using something else yesterday that did not work without the import line.

@pturmel

Don't convert to string! (In the dataset. Convert to string at the point of display only.)

OK! Not displaying it in Ignition. I'm wanting to POST it into an Azure database. I'm waiting to hear from them if the timestamp format matters they did say it was ISO8601. I spent a few hours trying to convert it back so I'm guessing they will not care.

[PGriffith]

There's probably some complications being added here because you're round-tripping data to Perspective; JSON doesn't really have a 'date' type, so we automatically coerce Java date objects into epoch timestamps when we send them over the wire, and it's possible something's getting confused with the return value of that.

More information about your script, where you're calling it, some screenshots, etc, would always be helpful.

For the question as posed, if you truly have one or more date columns in your dataset, and if you truly need to coerce those into a string following some fixed formatting pattern, you can use system.dataset.formatDates to do so.
If you're sending a POST to Azure, you'll likely need to then transform that dataset to a specific JSON format Azure expects, but that's another level down.

[Kevin.Herron]

@r123

I need the dataset to be in what I think is JSON format and this seems to work

@r123

I'm wanting to POST it into an Azure database

Are you sure you want a Dataset here? Datasets are a table data structure that are specific to Ignition.

If you're POSTing somewhere, and it wants JSON, you should be building a dictionary to encode to JSON.

[pascal.fragnoud]

I should have said "nothing in the dataset has been converted".
You're seeing different things, not because the underlying data is different, but because the representation is different.

@r123

I've been using self.parent.parent.getChild("Label_1").props.text = MyDataset to see what is in the datasets on a Perspective screen

Use a table instead.
Or if you can get the dataset to the script console somehow, you can use the pprint library to pretty print it.

[r123]

Sorry, I'm sure I'm missing something and not doing this the correct way.

Many things to look at but just looking at the date format changing. I thought passing the dataset to the Label object was a way to just see the text in the dataset as is without any formatting. It looks like it is, looks like one long string.

tagDataset - As shown in the Label
["Sys02","Sec110","Unit_1","Current",4.515449908473694,"2023-08-01T17:50:00.000Z"]

MyDataset - As shown in the Label
{"system":"Sys02","sector":"Sec110","unit":"Unit_1","tagname":"Current","Value":4.515449908473694,"timestamp":1690912200000}

You're saying that in reality the what looks like a string
,"2023-08-01T17:50:00.000Z"
in tagDataset is still the same in MyDataset but that the Label object is changing it to
,"timestamp":1690912200000
?
Putting them both into tables, tagDataset shows a date like 08/01/2023

and MyDataset shows a number like 1,690,912,200,000.

If I convert it to string first then after the convertion to MyDataset I get this:
,"timestamp":"2023-08-01 12:51:00.0"

[lrose]

They are the same, one is just the Millisecond since Epoch representation of the date.

Note, I'm in a different timezone and didn't convert the date to account, that is why mine show 13 instead of 12.

[r123]

@lrose

one is just the Millisecond since Epoch representation

I get that, it's changed into something that can be changed back but not the same as it was and not what is currently needed. So the format is different in the second converted dataset, it's not just the Label or Table that are changing how it looks in Perspective?

At first I did try and change the format back with that or a similar command but I found the convert to string example to stop it from changing the format.

Now I need to work out if I need a dataset or not, or maybe a "dictionary to encode to JSON"?

The thought was to create a dataset for all the values needed and then just POST the dataset to the Azure database. This would happen every 10 minutes with the data from 20 minutes ago. Seemed better then hundreds of POSTs in a loop. Going to see if I can get both working with just one tag.

[pturmel]

@r123

then just POST the dataset to the Azure database

It is extremely unlikely that Azure would understand an Ignition dataset.

Construct a dictionary or list of dictionaries and let system.net.httpClient() do the json encoding when POSTing.

[pascal.fragnoud]

Can you give us a sample of the data you're using ?

[r123]

@pturmel

It is extremely unlikely that Azure would understand an Ignition dataset.

It had me going though. The text output of the dataset shown in the Label looks just like what I need. In fact, I can copy the text from the Label and paste it into a list, then use the list in the POST and it works but if I use the dataset I get error 400 as it's just passing "Dataset [2R ⅹ 6C]".

TagList = [{system:TT_02,sector:110,unit:Unit_A_1,point:Current_A,Value:5.0116116230028345,timestamp:2023-08-01T12:50:00.500Z},
{system:TT_02,sector:110,unit:Unit_B_1,point:Current_A,Value:5.038061565493177,timestamp:2023-08-01T12:55:00.550Z}]

Response = client.post(url, data=TagList , username="admin", password="#######")

I guess I'll start looking at dictionaries in the morning.

I just tried to concatenate the headers\keys on to the tagDataset.append to bypass the dataset conversion MyDataset = system.dataset.toDataSet(tagHeaders, tagDataset), that gets me what I need but no way to access it, but it keeps changing the quotes into slashes or escape characters.

So this:

result  = """ "system":" """
print(result)

Gets me this:

>>> 
 "system":" 
>>>

But when used in the append:
tagDataset.append([result + system_ParentFolder, sector_ParentFolder, unit_ParentFolder, browseTag.name, value, timestamp_str])

It becomes: ["system\":\"02"...

[pascal.fragnoud]

It looks to me like you're still confusing data and its representation. What a label/console shows is NOT what the data is.
I'm not sure how ignition itself deals with it, but for python, what you're shown when using print is what the object's __str__() method returns. Which could be anything:

class Something:
	def __init__(self, value):
		self.value = value

s = Something(42)

s			# uses __repr__
print s		# uses __str__ if it exists, __repr__ otherwise

<main.Something instance at 0x7>
<main.Something instance at 0x7>

class Something:
	def __init__(self, value):
		self.value = value
	
	def __repr__(self):
		return "instance of Something with value {}".format(self.value)
	
	def __str__(self):
		return str(self.value * 2)

s = Something(42)

s			# uses __repr__
print s		# uses __str__ if it exists, __repr__ otherwise

instance of Something with value 42
84

the slashes, quotes etc are usually part only of the data representation.
See what happens when we use a string and use its built-in str() and repr methods:

class Something:
	def __init__(self, value):
		self.value = value
	
	def __repr__(self):
		return self.value.__repr__()
	
	def __str__(self):
		return self.value.__str__()


s = Something("foo")

s
print s

'foo'
foo

Try using this class with a dict, a dataset, etc.