# java.sql.Timestamp value can't be added to dataset **URL:** \ **Category:** Ignition\ **Created:** [January 10, 2022, 9:34pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850 "2022-01-10T21:34:39Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![ShaneH](https://avatars.discourse-cdn.com/v4/letter/s/3e96dc/32.png) [@ShaneH](https://forum.inductiveautomation.com/u/ShaneH)\ **Post date:** [January 10, 2022, 9:34pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/1 "2022-01-10T21:34:39Z")
Perspective 8.1.13 We have a gateway script that creates a dataset using system.dataset.toDataSet(). The data is a collection of values from DB queries, including some date/time values. Recently, after updating to 8.1.13, date/time values from the DB seem to randomly get returned as a java.sql.Timestamp types. When they do, system.dataset.toDataSet() complains that these types can’t be inserted into a dataset. I added code to convert them to java.util.Date types (using system.date.fromMillis()) … but this seems to be something that changed recently and I’m trying to figure out the cause. Any ideas? Were there changes to the default JDBC driver shipped with Ignition that might cause this? Something else? Thanks. -Shane ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [January 10, 2022, 9:40pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/2 "2022-01-10T21:40:06Z")
They were probably always sometimes returning `java.sql.Timestamp` for whatever reason. What changed is that strict validation on the column types of a Dataset were added in 8.1.13 because unexpected/non-serializable types would cause errors in the tag system on a subsequent restart. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 10, 2022, 10:35pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/3 "2022-01-10T22:35:59Z")
Please add that one to the list. If you want more precision than milliseconds, like you’d get from event recorders and similar high-speed devices, you need that column type. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [January 10, 2022, 10:40pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/4 "2022-01-10T22:40:16Z")
Just wrote up a ticket for it. ---
**Author:** ![ShaneH](https://avatars.discourse-cdn.com/v4/letter/s/3e96dc/32.png) [@ShaneH](https://forum.inductiveautomation.com/u/ShaneH)\ **Post date:** [January 10, 2022, 10:47pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/5 "2022-01-10T22:47:50Z")
This is the only comment I can find in the 8.1.13 release notes that I think covers what you’re referring to:. “Dataset types that are stored by ignition must match those allowed by the designer. This change was made to prevent serialization errors from unknown Dataset values.” Is there any place I can find more details? For instance, what are the specific types allowed? Thanks. -Shane ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [January 10, 2022, 10:56pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/6 "2022-01-10T22:56:21Z")
Well, the tag types allowed by the Designer are listed here: [Tag Data Types - Ignition User Manual 8.1 - Ignition Documentation](https://docs.inductiveautomation.com/display/DOC81/Tag+Data+Types) Right now the whitelist in the code is this: ```auto public static final Set> STORABLE_TYPES = Set.of( Date.class, String.class, Integer.class, Long.class, Short.class, Float.class, Double.class, Boolean.class, Color.class ); ``` ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 10, 2022, 11:38pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/7 "2022-01-10T23:38:00Z")
I presume this will choke on `Object.class` used in scripts to allow different types by row? ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 10, 2022, 11:39pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/8 "2022-01-10T23:39:33Z")
Please consider applying the column types check to writes to tags, not to Dataset creation. ---
**Author:** ![Kevin.Herron](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kevin.herron/32/7259_2.png) [@Kevin.Herron](https://forum.inductiveautomation.com/u/Kevin.Herron)\ **Post date:** [January 10, 2022, 11:40pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/9 "2022-01-10T23:40:50Z")
That’s what is happening now, you can do anything you want in scripting still. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [January 10, 2022, 11:41pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/10 "2022-01-10T23:41:53Z")
That would explain why it hasn’t blown up on me yet. ---
**Author:** ![ShaneH](https://avatars.discourse-cdn.com/v4/letter/s/3e96dc/32.png) [@ShaneH](https://forum.inductiveautomation.com/u/ShaneH)\ **Post date:** [January 11, 2022, 12:44pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/11 "2022-01-11T12:44:36Z")
Except when you call system.dataset.toDataSet() in a script ☹ ---
**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:** [January 11, 2022, 3:17pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/12 "2022-01-11T15:17:22Z")
The whitelist of dataset types is not going to be invoked when you create a dataset. Writing that dataset to a tag is where the operation would fail. The whitelist is used in exactly two places: 1. The manual dataset editing dialog, when you add a new column to a dataset. 2. In the tag system, when a new write happens to a tag: ![image](https://us1.discourse-cdn.com/inductiveautomation/original/3X/4/1/413d26291dc59bf5ea664ea8a6254097ee64682b.png) Post a script where `system.dataset.toDataSet` is failing due to this type coercion, and I’ll either show you a different bug, unrelated to this whitelist, or show you where you’re writing that dataset to a tag. ---
**Author:** ![ShaneH](https://avatars.discourse-cdn.com/v4/letter/s/3e96dc/32.png) [@ShaneH](https://forum.inductiveautomation.com/u/ShaneH)\ **Post date:** [January 11, 2022, 4:27pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/14 "2022-01-11T16:27:41Z")
As usual, you are of course right! ---
**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:** [January 11, 2022, 5:07pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/15 "2022-01-11T17:07:19Z")
Hah, hardly. I just have the benefit of being able to read the code 🙂 ---
**Author:** ![ShaneH](https://avatars.discourse-cdn.com/v4/letter/s/3e96dc/32.png) [@ShaneH](https://forum.inductiveautomation.com/u/ShaneH)\ **Post date:** [January 11, 2022, 7:56pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/16 "2022-01-11T19:56:56Z")
Not seen this, but trying to check my code for other instances of this issue: If I have a tag setup with a ‘Query’ value source, and a ‘Dataset’ data type, and the query (for what ever reason) returns a java.sql.Timestamp type … will the tag fault as if I had tried to do a system.tag.write(ds) to the tag with a Timestamp type value in ds? ---
**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:** [January 11, 2022, 8:34pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/17 "2022-01-11T20:34:17Z")
Yes, the error will be thrown whenever a dataset containing types outside the whitelist makes it to the tag system. So the query tag would throw it directly when the query returns, _or_ writing from scripting would throw it. ---
**Author:** ![braydon.hancock](https://avatars.discourse-cdn.com/v4/letter/b/c67d28/32.png) [@braydon.hancock](https://forum.inductiveautomation.com/u/braydon.hancock)\ **Post date:** [February 15, 2022, 5:52pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/18 "2022-02-15T17:52:08Z")
Is there a known workaround for using system.tag.queryHistory in a tag script which writes the result to a dataset tag? The invalid datatype for dataset is breaking this functionality. We are using that script to query an array of tags on a periodic basis. Those tags are then read from a remote gateway and used to display trend data. This had to be done to reduce the load on the gateway network. ---
**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:** [February 15, 2022, 6:30pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/19 "2022-02-15T18:30:10Z")
I would wait for 8.1.15 where this is fixed (estimated release in the beginning of March). If that’s not an option, you could use a document tag instead of a dataset, perhaps, but there’s no direct workaround. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [February 15, 2022, 7:21pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/20 "2022-02-15T19:21:24Z")
Well, you can reprocess the dataset to coerce values in Date columns to be real dates. ---
**Author:** ![lane.duncan](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/lane.duncan/32/83700_2.png) [@lane.duncan](https://forum.inductiveautomation.com/u/lane.duncan)\ **Post date:** [March 1, 2022, 10:03pm UTC](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850/21 "2022-03-01T22:03:53Z")
> [@ShaneH](#): > > Except when you call system.dataset.toDataSet() in a script ☹ My script just broke because I tried to make a dataset column a list data type (user.getRoles). Easily fixed by converting to a string, but it sure would be nice if an error were thrown when trying to create an invalid dataset column. [Next page](https://forum.inductiveautomation.com/t/java-sql-timestamp-value-cant-be-added-to-dataset/54850.md?page=2)