JSON, Cache and Date/Time

There are good options for what you want available in 2016.2, and possibly better answers for SQL -> JSON after that.

In 2016.2, %RegisteredObject also supports $toJSON and $fromJSON, so there won't be any need to use %ZEN.Auxiliary.jsonProvider to do that conversion. Under the hood, the path is really RegisteredObject -> Dynamic Object (via $compose) -> JSON, and JSON -> Dynamic Object -> RegisteredObject (via $compose)

Therefore, the behavior of $toJSON and $fromJSON can be modified for %RegisteredObject subclasses by overriding (typically) %ToDynamicObject and %FromObject. Here's an example that might serve as a useful starting point for Object -> JSON/JSON -> Object on 2016.2+:

Class DCDemo.JSONDateTime Extends (%Persistent, %Populate)
{

Property Name As %String;

Property DateField As %Date;

Property "Time_Stamp_Field" As %TimeStamp;

Property TimeField As %Time;

ClassMethod Run()
{
    Do ..%KillExtent()
    Do ..Populate(1)
    
    Set tObj = ..%OpenId(1)
    Write "Object ID 1",!
    zw tObj
    Write !
    
    Set tJSON = tObj.$toJSON()
    Write "JSON for that object:",!
    Write tJSON,!,!
    
    Set tObj2 = ..$fromJSON(tJSON)
    Write "Object from that JSON:",!
    zw tObj2
    Write !
}

Method %ToDynamicObject(target As %Object = "", ignoreUnknown = 0) [ ServerOnly = 1 ]
{
    Set tObj = ##super(target,ignoreUnknown)
    Do ..DateTimeToISO8601(tObj)
    Quit tObj
}

ClassMethod %FromObject(source = "", target = "", laxMode As %Integer = 1) As %RegisteredObject [ ServerOnly = 1 ]
{
    Set tObj = ##super(source,target,laxMode)
    If source.%IsA("%Library.AbstractObject") {
        Do ..ISO8601ToDateTime(tObj)
    }
    Quit tObj
}

ClassMethod DateTimeToISO8601(pObj As %Library.AbstractObject) [ CodeMode = objectgenerator ]
{
    #dim tProp As %Dictionary.CompiledProperty
    Set tKey = ""
    For {
        Set tProp = %compiledclass.Properties.GetNext(.tKey)
        Quit:tKey=""
        
        If (tProp.Type '= "") && 'tProp.ReadOnly && 'tProp.Calculated {
            Set tType = tProp.Type
            Set tExpr = ""
            If $ClassMethod(tType,"%Extends","%Library.Date") {
                Set tExpr = "Set %arg = $zd(%arg,3)"
            } ElseIf $ClassMethod(tType,"%Extends","%Library.Time") {
                Set tExpr = "Set %arg = $zt(%arg,1)"
            } ElseIf $ClassMethod(tType,"%Extends","%Library.TimeStamp") {
                Set tExpr = "Set %arg = $Case(%arg,"""":"""",:$Replace(%arg,"" "",""T"")_""Z"")"
            }
            Do:tExpr'="" %code.WriteLine($c(9)_$Replace(tExpr,"%arg","pObj."_$$$QN(tProp.Name)))
        }
    }
}

ClassMethod ISO8601ToDateTime(pObj As DCDemo.JSONDateTime) [ CodeMode = objectgenerator ]
{
    #dim tProp As %Dictionary.CompiledProperty
    Set tKey = ""
    For {
        Set tProp = %compiledclass.Properties.GetNext(.tKey)
        Quit:tKey=""
        
        If (tProp.Type '= "") && 'tProp.ReadOnly && 'tProp.Calculated {
            Set tType = tProp.Type
            Set tExpr = ""
            If $ClassMethod(tType,"%Extends","%Library.Date") {
                Set tExpr = "Set %arg = $zdh(%arg,3)"
            } ElseIf $ClassMethod(tType,"%Extends","%Library.Time") {
                Set tExpr = "Set %arg = $zth(%arg,1)"
            } ElseIf $ClassMethod(tType,"%Extends","%Library.TimeStamp") {
                Set tExpr = "Set %arg = $Extract($Replace(%arg,""T"","" ""),1,*-1)"
            }
            Do:tExpr'="" %code.WriteLine($c(9)_$Replace(tExpr,"%arg","pObj."_$$$QN(tProp.Name)))
        }
    }
}

}

The output of this is:

USER>d ##class(DCDemo.JSONDateTime).Run()
Object ID 1
tObj=<OBJECT REFERENCE>[1@DCDemo.JSONDateTime]
+----------------- general information ---------------
|      oref value: 1
|      class name: DCDemo.JSONDateTime
|           %%OID: $lb("1","DCDemo.JSONDateTime")
| reference count: 2
+----------------- attribute values ------------------
|       %Concurrency = 1  <Set>
|          DateField = 40424
|               Name = "North,Richard G."
|          TimeField = 74813
|   Time_Stamp_Field = "1963-11-18 01:49:29"
+-----------------------------------------------------
 
JSON for that object:
{"$CLASSNAME":"DCDemo.JSONDateTime","$REFERENCE":"1","DateField":"1951-09-05","Name":"North,Richard G.","TimeField":"20:46:53","Time_Stamp_Field":"1963-11-18T01:49:29Z"}
 
Object from that JSON:
tObj2=<OBJECT REFERENCE>[4@DCDemo.JSONDateTime]
+----------------- general information ---------------
|      oref value: 4
|      class name: DCDemo.JSONDateTime
| reference count: 2
+----------------- attribute values ------------------
|       %Concurrency = 1  <Set>
|          DateField = 40424
|               Name = "North,Richard G."
|          TimeField = 74813
|   Time_Stamp_Field = "1963-11-18 01:49:29"
+-----------------------------------------------------

The matter of SQL -> JSON is a bit more complicated. ODBC select mode for SQL is similar to ISO 8601, but not completely (the timestamp format is different). One option would be to create a class (extending %RegisteredObject) to represent a query result with date/time fields in ISO 8601 format, and to override the same methods in it so that:

• It can be $compose'd from a %SQL.IResultSet (done in %FromObject)
• Based on query column metadata, dates/times/timestamps are converted to the correct format when the object is represented as a %Object/%Array or, indirectly, in JSON (done in %ToDynamicObject / %ToDynamicArray).

This could probably be done in 2016.2, but might be less work to accomplish in a future version when SQL result sets support $fromJSON/$toJSON. (I think this plan was mentioned in a different post.)

I suppose there are some possible complications with all this, depending on whether times/timestamps in your application are actually local or UTC. (Or worse, a mix...)