Query Caching not working after migrating to ver12 for DateTime columns

[Former Member]

I have an applet that charts production numbers over time. The query template is cached for 1 min. When the applet uses fresh data the applet charts properly and the underlying data has accurate values in the date/time columns. But if the applet charts from cached data the chartu2019s x-axis (date/time) shows an incorrect date range and the underlying data has inaccurate values in the date/time columns but other columns values are accurate.

[jcgood25]

Any JavaScript getting in the way?

Add the following line to the applet and then replicate your issue taking a look at the differing http requests in the java console. Notice any differences in the requests?

<PARAM NAME="Trace" VALUE="true">

Regards,

Jeremy

[Former Member]

The first is fresh data. The second is a refresh with cached data. Only difference is in the fresh data trace there is an additional config - ApplyTZOffset=true

Post data [Connector=&Server=WW Runtime&Service=&Mode=FixedQuery&Method=&CacheDurationUnits=M&CacheDuration=5&IsCachable=true&Param.1=currentShift&Param.2=0&Param.3=09%2F16%2F200800%3A00&Param.4=09%2F16%2F200800%3A00&Param.5=Y9129&Param.6=trend&Param.7=details&Param.8=15&Param.9=0&Param.10=&Param.11=&Param.12=&Param.13=&Param.14=&Param.15=&Param.16=&Param.17=&Param.18=&Param.19=&Param.20=&Param.21=&Param.22=&Param.23=&Param.24=&Param.25=&Param.26=&Param.27=&Param.28=&Param.29=&Param.30=&Param.31=&Param.32=&Query=EXECRuntime.dbo.getOEETrendController%27%5BParam.1%5D%27%2C%5BParam.2%5D%2C%27%5BParam.3%5D%27%2C%27%5BParam.4%5D%27%2C%27%5BParam.5%5D%27%2C%27%5BParam.6%5D%27%2C%27%5BParam.7%5D%27%2C%5BParam.8%5D%2C%5BParam.9%5D&QueryParams=&NumberFormat=0.00&DateFormat=MM-dd-yyyy+HH%3Amm%3Ass&RowCount=120000&Content-Type=raw/binary&ApplyTZOffset=true&AllowBuffering=false&Comment=]

4390

Post data [Connector=&Server=WW Runtime&Service=&Mode=FixedQuery&Method=&CacheDurationUnits=M&CacheDuration=5&IsCachable=true&Param.1=currentShift&Param.2=0&Param.3=09%2F16%2F200800%3A00&Param.4=09%2F16%2F200800%3A00&Param.5=Y9129&Param.6=trend&Param.7=details&Param.8=15&Param.9=0&Param.10=&Param.11=&Param.12=&Param.13=&Param.14=&Param.15=&Param.16=&Param.17=&Param.18=&Param.19=&Param.20=&Param.21=&Param.22=&Param.23=&Param.24=&Param.25=&Param.26=&Param.27=&Param.28=&Param.29=&Param.30=&Param.31=&Param.32=&Query=EXECRuntime.dbo.getOEETrendController%27%5BParam.1%5D%27%2C%5BParam.2%5D%2C%27%5BParam.3%5D%27%2C%27%5BParam.4%5D%27%2C%27%5BParam.5%5D%27%2C%27%5BParam.6%5D%27%2C%27%5BParam.7%5D%27%2C%5BParam.8%5D%2C%5BParam.9%5D&QueryParams=&NumberFormat=0.00&DateFormat=MM-dd-yyyy+HH%3Amm%3Ass&RowCount=120000&Content-Type=raw/binary&AllowBuffering=false&Comment=]

31

[jcgood25]

Assumedly the 4390ms for the first query to return versus the 31ms for the second query to return means that the query caching is working.

Within the 5 minute cache duration you have set, if you do a right-click...data details I would assume it returns fast as well. This should also be the same dataset being displayed by the iChart - how do you have the iChart configured for the xaxis?

Note: Boosting up the RowCount to 120000 is a bit excessive since the chart can only plot one data point per screen pixel (which would make a very...very wide iChart).

Regards,

Jeremy

[Former Member]

The data is the same in both except for the date time columns. The date time in the fresh data shows a start time of 15:00 time (correct). The cached data shows a date time with a start time of 18:00 (incorrect).

[Former Member]

x-axis value column = datetime column

date format = MM/dd

time format = HH:mm

does this need to match the format of the datetime column?

[Former Member]

In the data, all datetime columns are consistently distorted by -5 hours when read from cache. Is there a global setting for time offset? The only place I have seen where it can be set is in virtual servers u2013 not being used.

[jcgood25]

No global setting for this - are you timestamping your query request in the stored procedure? Is the relational database server in the same timezone as the MII server?

[Former Member]

No timestamping. They are not in the same timezone - The database server is Eastern (GMT-05:00) and the webserver is Central time(GMT-06:00).