How to convert JSON back to date object

drojas · October 16, 2025, 9:25pm

Does anyone know how to convert this strange date format back to a date object?

        "timestamp": {
          "$": [
            "ts",
            192,
            1760647890223
          ],
          "$ts": 1759156706683
        }

The full story is that I load some data from a database to a perspective custom property and it looks fine in the designer. The data comes from an SQL query returned as a dataset, then transformed into a JSON object for later use

When I iterate through the property, I use the .get(“timestamp”) from the row object which returns the strange format above. The issue is when I try to insert data back using runPrepUpdate(), I get an “Unable to decode arguments”…but if I edit this timestamp to have a value that looks like this '2025-09-30T12:00:00Z’, the runPrepUpdate() works just fine.

Here is the error for completeness:

[3, 1.5, {'$ts': 1759233600000L, '$': ['ts', 192, 1760644157017L]}]
Java Traceback:
Traceback (most recent call last):
  File "<input>", line 150, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO mytable (tagid, value, timestamp) VALUES (?,?,?), [3,1.5, {'$ts': 1759233600000L, '$': ['ts', 192, 1760644157017L]}], , , false, false)


	at org.python.core.Py.JavaError(Py.java:545)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

	at org.python.core.PyReflectedFunction.__call__(PyReflectedFunction.java:190)

	at com.inductiveautomation.ignition.common.script.ScriptManager$ReflectedInstanceFunction.__call__(ScriptManager.java:553)

	at org.python.core.PyObject.__call__(PyObject.java:477)

	at org.python.core.PyObject.__call__(PyObject.java:481)

	at org.python.pycode._pyx363.f$0(<input>:109)

	at org.python.pycode._pyx363.call_function(<input>)

	at org.python.core.PyTableCode.call(PyTableCode.java:173)

	at org.python.core.PyCode.call(PyCode.java:18)

	at org.python.core.Py.runCode(Py.java:1703)

	at org.python.core.Py.exec(Py.java:1747)

	at org.python.util.PythonInterpreter.exec(PythonInterpreter.java:277)

	at org.python.util.InteractiveInterpreter.runcode(InteractiveInterpreter.java:130)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:628)

	at com.inductiveautomation.ignition.designer.gui.tools.jythonconsole.JythonConsole$ConsoleWorker.doInBackground(JythonConsole.java:616)

	at java.desktop/javax.swing.SwingWorker$1.call(Unknown Source)

	at java.base/java.util.concurrent.FutureTask.run(Unknown Source)

	at java.desktop/javax.swing.SwingWorker.run(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)

	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)

	at java.base/java.lang.Thread.run(Unknown Source)

Caused by: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO mytable (tagid, value, timestamp) VALUES (?,?,?), [3,1.5, {'$ts': 1759233600000L, '$': ['ts', 192, 1760644157017L]}], , , false, false)

	... 26 more

Caused by: com.inductiveautomation.ignition.client.gateway_interface.GatewayException: Unable to decode arguments

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.newGatewayException(GatewayInterface.java:360)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:334)

	at com.inductiveautomation.ignition.client.gateway_interface.GatewayInterface.sendMessage(GatewayInterface.java:287)

	at com.inductiveautomation.ignition.client.script.ClientDBUtilities._runPrepStmt(ClientDBUtilities.java:293)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:261)

	... 24 more

Caused by: java.io.InvalidClassException: failed to read class descriptor

	at java.io.ObjectInputStream.readNonProxyDesc(null)

	at java.io.ObjectInputStream.readClassDesc(null)

	at java.io.ObjectInputStream.readOrdinaryObject(null)

	at java.io.ObjectInputStream.readObject0(null)

	at java.io.ObjectInputStream.readArray(null)

	at java.io.ObjectInputStream.readObject0(null)

	at java.io.ObjectInputStream.readObject(null)

	at java.io.ObjectInputStream.readObject(null)

	at com.inductiveautomation.ignition.common.Base64.decodeToObjectFragile(Base64.java:985)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.RunPrepStmt.runUpdateQuery(RunPrepStmt.java:43)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractUpdateQuery.run(AbstractUpdateQuery.java:26)

	at com.inductiveautomation.ignition.gateway.servlets.gateway.functions.AbstractDBAction.invoke(AbstractDBAction.java:66)

	at com.inductiveautomation.ignition.gateway.servlets.Gateway.doPost(Gateway.java:435)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:523)

	at javax.servlet.http.HttpServlet.service(HttpServlet.java:590)

	at com.inductiveautomation.ignition.gateway.bootstrap.MapServlet.service(MapServlet.java:86)

	at org.eclipse.jetty.servlet.ServletHolder$NotAsync.service(ServletHolder.java:1410)

	at org.eclipse.jetty.servlet.ServletHolder.handle(ServletHolder.java:764)

	at org.eclipse.jetty.servlet.ServletHandler$ChainEnd.doFilter(ServletHandler.java:1665)

	at org.eclipse.jetty.servlet.ServletHandler.doHandle(ServletHandler.java:527)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:131)

	at org.eclipse.jetty.security.SecurityHandler.handle(SecurityHandler.java:598)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:223)

	at org.eclipse.jetty.server.session.SessionHandler.doHandle(SessionHandler.java:1580)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextHandle(ScopedHandler.java:221)

	at org.eclipse.jetty.server.handler.ContextHandler.doHandle(ContextHandler.java:1384)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:176)

	at org.eclipse.jetty.servlet.ServletHandler.doScope(ServletHandler.java:484)

	at org.eclipse.jetty.server.session.SessionHandler.doScope(SessionHandler.java:1553)

	at org.eclipse.jetty.server.handler.ScopedHandler.nextScope(ScopedHandler.java:174)

	at org.eclipse.jetty.server.handler.ContextHandler.doScope(ContextHandler.java:1306)

	at org.eclipse.jetty.server.handler.ScopedHandler.handle(ScopedHandler.java:129)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at com.inductiveautomation.catapult.handlers.RemoteHostNameLookupHandler.handle(RemoteHostNameLookupHandler.java:121)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at org.eclipse.jetty.rewrite.handler.RewriteHandler.handle(RewriteHandler.java:301)

	at org.eclipse.jetty.server.handler.HandlerList.handle(HandlerList.java:51)

	at org.eclipse.jetty.server.handler.HandlerCollection.handle(HandlerCollection.java:141)

	at org.eclipse.jetty.server.handler.HandlerWrapper.handle(HandlerWrapper.java:122)

	at org.eclipse.jetty.server.Server.handle(Server.java:563)

	at org.eclipse.jetty.server.HttpChannel$RequestDispatchable.dispatch(HttpChannel.java:1598)

	at org.eclipse.jetty.server.HttpChannel.dispatch(HttpChannel.java:753)

	at org.eclipse.jetty.server.HttpChannel.handle(HttpChannel.java:501)

	at org.eclipse.jetty.server.HttpConnection.onFillable(HttpConnection.java:287)

	at org.eclipse.jetty.io.AbstractConnection$ReadCallback.succeeded(AbstractConnection.java:314)

	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)

	at org.eclipse.jetty.io.ssl.SslConnection$DecryptedEndPoint.onFillable(SslConnection.java:558)

	at org.eclipse.jetty.io.ssl.SslConnection.onFillable(SslConnection.java:379)

	at org.eclipse.jetty.io.ssl.SslConnection$2.succeeded(SslConnection.java:146)

	at org.eclipse.jetty.io.FillInterest.fillable(FillInterest.java:100)

	at org.eclipse.jetty.io.SelectableChannelEndPoint$1.run(SelectableChannelEndPoint.java:53)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.runTask(AdaptiveExecutionStrategy.java:421)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.consumeTask(AdaptiveExecutionStrategy.java:390)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.tryProduce(AdaptiveExecutionStrategy.java:277)

	at org.eclipse.jetty.util.thread.strategy.AdaptiveExecutionStrategy.run(AdaptiveExecutionStrategy.java:199)

	at org.eclipse.jetty.util.thread.ReservedThreadExecutor$ReservedThread.run(ReservedThreadExecutor.java:411)

	at org.eclipse.jetty.util.thread.QueuedThreadPool.runJob(QueuedThreadPool.java:969)

	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.doRunJob(QueuedThreadPool.java:1194)

	at org.eclipse.jetty.util.thread.QueuedThreadPool$Runner.run(QueuedThreadPool.java:1149)

	at java.lang.Thread.run(null)

Traceback (most recent call last):
  File "<input>", line 150, in <module>
	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392)

	at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.runPrepUpdate(AbstractDBUtilities.java:268)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)

	at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)

	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)

	at java.base/java.lang.reflect.Method.invoke(Unknown Source)

java.lang.Exception: java.lang.Exception: Error executing system.db.runPrepUpdate(INSERT INTO mytable (tagid, value, timestamp) VALUES (?,?,?), [3, 1.5, {'$ts': 1759233600000L, '$': ['ts', 192, 1760644157017L]}], , , false, false)
>>>

David_Stone · October 16, 2025, 9:34pm

This is not strange, this is the standard way to store a timestamp.

" The Unix epoch (or Unix time or POSIX time or Unix timestamp ) is the number of seconds that have elapsed since January 1, 1970 (midnight UTC/GMT), not counting leap seconds (in ISO 8601: 1970-01-01T00:00:00Z)."

system.date.fromMillis(1760647890223 * 1000) will get you back to a date object.

drojas · October 16, 2025, 9:38pm

Sure, I am aware of Unix epoch…I guess my question is can the whole timestamp object (with its “$”: [“ts”,192, and the “$ts”: structure) be converted directly…or do I have to go in and fetch the “$ts” key’s value from that object.

David_Stone · October 16, 2025, 9:52pm

It looks like you are converting a whole timestamp structure from a tag, not just the value itself, the 192 refers to tag quality:

You will not need to manually do anything with the tag quality value as it will be automatically applied if you write it to a tag.

paul-griffith · October 16, 2025, 10:41pm

The $ + $ts is Perspective's proprietary "dollar qualified" encoding scheme used to pack extra metadata in to values we send to the frontend, since JSON doesn't support 'rich' types like qualified values that bundle value + quality + timestamp.

The inner $ts is the epoch millis that the value contains. There's no static utility function to import that will reverse the qualification for you, because the logic to do that lives on the frontend in Typescript code, so you'll have to script your own workaround in Jython.