system.dataset.exportCSV - fractional seconds dropped

Ignition
,
1

ignition 7.9
mySQL 5.7.22
PLCs : S7-1200

Hi All

I’ve written a script to pull data from an SQL table and export it as a csv. My table has a ‘Timestamp’ column is defined as DATETIME(3). When I open the csv generated by my code, there are no fractional seconds digits in the Timestamp column. I have manually exported the data to .csv via mySQL workbench and the digits are retained as desired.

I’ve pasted an excerpt from my script below. Can anyone suggest a method for retaining the millisecond data? I notice there is a system.dataset.toExcel command. However, I have not had time to test this yet.

Thanks for looking.

# Build a SQL query. Note (`) character required for headers with spaces.
query = system.db.runQuery("SELECT Timestamp, `CF Number`, `Roll Number`, `Roll Diam (mm)`, `Log Posn`, `Width Posn (mm)`, `A112ZT001 (mm)`, `A112ZT002 (mm)`, `Thickness (mm)` FROM data_fthick")
# Get the data from the SQL DB
results = system.dataset.toDataSet(query)
# Use system.dataset.toCSV to turn the dataset into a CSV string
sqlData = system.dataset.toCSV(dataset = results, showHeaders = True, forExport = 0)
# Append the data to the file
system.file.writeFile(filePath, sqlData, 1)
2

Interesting. Even running the query in the query browser drops the milliseconds so the system.dataset.toExcel won’t do much for you. As a temporary workaround you can also select MILLISECONDS(Timestamp) and append this to the time.

3

Thanks for the quick reply! Please can you explain more about how I would append milliseconds to the time? Cheers.

4

So, looking at the code, it looks like this is a consequence of the way the toCSV function delegates operations - it defers to your locale’s default date/time formatting when it writes a date to file.
There are two ways to fix it:

  1. Write your own CSV exporting logic (ie, using the Jython csv module). There’s nothing wrong with that, but it won’t accept an Ignition dataset quite as easily.
  2. In your query, select the timestamp column as a string in the format you want the output to have, eg:
select date_format(`Timestamp`, "%y-%m-%d %H-%M-%S-%f"), `CF_number`, etc
1 Like
5

Perfect! Many thanks.

My modified code is as follows

cheers

# Build an SQL query. Note (`) character required for headers with spaces.
query = system.db.runQuery("SELECT date_format(`Timestamp`, '%y/%m/%d %h:%i:%s.%f'), `CF Number`, `Roll Number`, `Roll Diam (mm)`, `Log Posn`, `Width Posn (mm)`, `A112ZT001 (mm)`, `A112ZT002 (mm)`, `Thickness (mm)` FROM data_fthick")
# Get the data from the SQL DB
results = system.dataset.toDataSet(query)
# Use system.dataset.toCSV to turn the dataset into a CSV string
sqlData = system.dataset.toCSV(dataset = results, showHeaders = True, forExport = 0)
# Append the data to the file
system.file.writeFile(filePath, sqlData, 1)
1 Like
6

Last question on this topic (I hope)!

My exported csv now has the header “date_format(Timestamp, ‘%y-%m-%d %h:%i:%s.%f’)” which is slightly unwieldy…

Is there a way to change this header in my script? Ideally, I would like the header to be “Timestamp”. I’ve had a read into datasets but there doesn’t seem to be a way of editing the headers.

7

Just change the SQL statement again:

SELECT date_format(`Timestamp`, '%y/%m/%d %h:%i:%s.%f') as Timestamp,
 `CF Number`, `Roll Number`, `Roll Diam (mm)`, `Log Posn`, `Width Posn (mm)`, `A112ZT001 (mm)`, `A112ZT002 (mm)`, `Thickness (mm)` FROM data_fthick
1 Like
8

got it. I just added "as Timestamp" to the query.

# Build an SQL query. Note (`) character required for headers with spaces.
query = system.db.runQuery("SELECT date_format(`Timestamp`, '%y/%m/%d %h:%i:%s.%f') as `Timestamp`, `CF Number`, `Roll Number`, `Roll Diam (mm)`, `Log Posn`, `Width Posn (mm)`, `A112ZT001 (mm)`, `A112ZT002 (mm)`, `Thickness (mm)` FROM data_fthick")
# Get the data from the SQL DB
results = system.dataset.toDataSet(query)
# Use system.dataset.toCSV to turn the dataset into a CSV string
sqlData = system.dataset.toCSV(dataset = results, showHeaders = True, forExport = 0)
# Append the data to the file
system.file.writeFile(filePath, sqlData, 1)

cheers.

1 Like