Getting a datetime to survive a JSON round trip to the database

Ignition
1
  • I'm storing a date as part of a schedule in a configuration database table and the field is a MySQL JSON type. It works well except ...
  • system.util.jsonEncode() results in the date being saved as a JSON string in format Oct 5, 2023, 12:00:00 AM.
  • When read back and run through system.util.json.Decode() it comes out as a string rather than a date.

I notice on json.org that there is no JSON date type.

Is there a simple fix for this or should I store as millis?

2

Whether you store it as millis or leave it as a String, you'll still need some additional step to turn that into a Date at some point, won't you?

1 Like
3

Anyway, I'd store it as millis since epoch UTC, to avoid any timezone/formatting/parsing issues.

If you were using a String you'd want it to be ISO 8601 UTC but that doesn't seem to be an option given whatever system.util.jsonEncode is doing.

1 Like
4

You might benefit from making this an object with value and type properties so you know how to transform it after decoding. Type info is lost once stored in JSON as a string.