# system.dataset.exportCSV - fractional seconds dropped **URL:** \ **Category:** Ignition\ **Tags:** ignition79, scripting\ **Created:** [November 5, 2018, 11:29am UTC](https://forum.inductiveautomation.com/t/system-dataset-exportcsv-fractional-seconds-dropped/20530 "2018-11-05T11:29:54Z")\ **Posts on this page:** 8\ **Page:** 1
**Author:** ![john.eichler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/john.eichler/32/24632_2.png) [@john.eichler](https://forum.inductiveautomation.com/u/john.eichler)\ **Post date:** [November 5, 2018, 11:29am UTC](https://forum.inductiveautomation.com/t/system-dataset-exportcsv-fractional-seconds-dropped/20530/1 "2018-11-05T11:29:54Z")
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. ```auto # 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) ``` ---
**Author:** ![ethomason](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/ethomason/32/43338_2.png) [@ethomason](https://forum.inductiveautomation.com/u/ethomason)\ **Post date:** [November 5, 2018, 4:34pm UTC](https://forum.inductiveautomation.com/t/system-dataset-exportcsv-fractional-seconds-dropped/20530/2 "2018-11-05T16:34:48Z")
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. ---
**Author:** ![john.eichler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/john.eichler/32/24632_2.png) [@john.eichler](https://forum.inductiveautomation.com/u/john.eichler)\ **Post date:** [November 5, 2018, 5:10pm UTC](https://forum.inductiveautomation.com/t/system-dataset-exportcsv-fractional-seconds-dropped/20530/3 "2018-11-05T17:10:10Z")
Thanks for the quick reply! Please can you explain more about how I would append milliseconds to the time? Cheers. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 5, 2018, 6:16pm UTC](https://forum.inductiveautomation.com/t/system-dataset-exportcsv-fractional-seconds-dropped/20530/4 "2018-11-05T18:16:35Z")
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](https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_date-format) you want the output to have, eg: ```auto select date_format(`Timestamp`, "%y-%m-%d %H-%M-%S-%f"), `CF_number`, etc ``` ---
**Author:** ![john.eichler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/john.eichler/32/24632_2.png) [@john.eichler](https://forum.inductiveautomation.com/u/john.eichler)\ **Post date:** [November 5, 2018, 9:25pm UTC](https://forum.inductiveautomation.com/t/system-dataset-exportcsv-fractional-seconds-dropped/20530/5 "2018-11-05T21:25:04Z")
Perfect! Many thanks. My modified code is as follows cheers ```auto # 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) ``` ---
**Author:** ![john.eichler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/john.eichler/32/24632_2.png) [@john.eichler](https://forum.inductiveautomation.com/u/john.eichler)\ **Post date:** [November 5, 2018, 9:40pm UTC](https://forum.inductiveautomation.com/t/system-dataset-exportcsv-fractional-seconds-dropped/20530/6 "2018-11-05T21:40:30Z")
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. ---
**Author:** ![paul-griffith](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/paul-griffith/32/37249_2.png) [@paul-griffith](https://forum.inductiveautomation.com/u/paul-griffith)\ **Post date:** [November 5, 2018, 9:45pm UTC](https://forum.inductiveautomation.com/t/system-dataset-exportcsv-fractional-seconds-dropped/20530/7 "2018-11-05T21:45:14Z")
Just change the SQL statement again: ```auto 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 ``` ---
**Author:** ![john.eichler](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/john.eichler/32/24632_2.png) [@john.eichler](https://forum.inductiveautomation.com/u/john.eichler)\ **Post date:** [November 5, 2018, 9:48pm UTC](https://forum.inductiveautomation.com/t/system-dataset-exportcsv-fractional-seconds-dropped/20530/8 "2018-11-05T21:48:16Z")
got it. I just added "as `Timestamp`" to the query. ```auto # 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.