Convert string column to Datetime

Ignition
1

I am retrieving certain data from 3rd party scada system using below query and need to added data set results to charts in ignition . The problem is the datetime column is coming as string not as data time format as per below snapshot . How can i convert it to Dattime format? .

SELECT DateTime , Value
FROM History 
WHERE TagName IN ('MWDashBoardIOs.C1_Co2')
AND wwRetrievalMode = 'Cyclic'
AND wwResolution = 86400000
AND wwQualityRule = 'Extended'
AND wwVersion = 'Latest'
AND DateTime >= '{Root Container.Popup Calendar 1.formattedDate}'
AND DateTime < '{Root Container.Popup Calendar.formattedDate}'

12
12828×243 27.5 KB

2

You can iterate through the rows of that dataset converting date strings to dates something like this:

# Create sample dataset with date strings.
headers = ['DateTime', 'Value']
data = [['2019-02-27 22:57:00.0000000',72181]]
data.append(['2019-02-28 22:57:00.0000000', 73365])
data.append(['2019-03-01 22:57:00.0000000', 73905])
dataset = system.dataset.toDataSet(headers, data)
# Convert date strings in first column to dates in new dataset.
newData = []
print 'Original rows:'
for row in range(dataset.rowCount):
	print type(dataset.getValueAt(row, 0)), dataset.getValueAt(row, 0), dataset.getValueAt(row, 1)
	# Make new row with date string converted to date.
	newData.append([system.date.parse(dataset.getValueAt(row, 0)), dataset.getValueAt(row, 1)])
newDataset = system.dataset.toDataSet(headers, newData)
# Confirm types are dates.
print 'Converted rows:'
for row in range(newDataset.rowCount):
	print type(newDataset.getValueAt(row, 0)), newDataset.getValueAt(row, 0), newDataset.getValueAt(row, 1)
# Write/assign newDataset to chart data.

Which gives this output in script console:

Original rows:
<type 'unicode'> 2019-02-27 22:57:00.0000000 72181
<type 'unicode'> 2019-02-28 22:57:00.0000000 73365
<type 'unicode'> 2019-03-01 22:57:00.0000000 73905
Converted rows:
<type 'java.util.Date'> Wed Feb 27 22:57:00 PST 2019 72181
<type 'java.util.Date'> Thu Feb 28 22:57:00 PST 2019 73365
<type 'java.util.Date'> Fri Mar 01 22:57:00 PST 2019 73905
3

You might find the view() expression function (from the free Simulation Aids module) a bit more convenient here. Assuming you move the original binding to a custom property called RawData, the expression for the data binding would be:

view("Select system.date.parse(DateTime) As DateTime, Value",
  {Root Container.Table.RawData})
1 Like