MySQL Bugs: #45858: Correlation graphs timestamps do not match if there is a timezone difference

Bug #45858	Correlation graphs timestamps do not match if there is a timezone difference
Submitted:	30 Jun 2009 15:29	Modified:	1 Sep 2009 14:16
Reporter:	Marcos Palacios	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Enterprise Monitor: Web	Severity:	S3 (Non-critical)
Version:	2.1.0.1071	OS:	Any
Assigned to:	Josh Sled	CPU Architecture:	Any

Description:
On the Query Analyzer page, the correlation graph (CG), query filter controls, and query table display four sets of time stamps:
- the ones on top of the CG selection lines,
- those in the X-axis of the CG,
- the Hours and Minutes menus in the query From/To filter, and
- those reported if there are 'no queries' in the query table area.

If the MEM User Preferences's Timezone is different than the box/OS/browser time zone (where the browser is running), the CG selection lines and the 'no queries' time stamps are incorrect.

If there is a timezone difference, the 1st and 4th sets of time stamps (see above) show the box's time, whereas the other two are in the MEM's User Preferences's time.

How to repeat:
- say the box is on EST, then set the User Preferences's Timezone to PST (any time difference suffices)
- go to the Query Analyzer page and use a CG to select a query time frame
- observe the difference in the sets of time stamps

Suggested fix:
Notes on possible fix:
I don't know what the right fix is, exactly.  It's messy to get 100% correct, especially around DST changes. IIRC from looking at it before, the problem there is around historical data.  Because while you can (easily) determine the current offset against UTC, it's harder if the graph is showing data from the last week.  But the general form of the fix is "add <MEM User> TZ offset to the response so the JS can compute the delta, and adjust the times to be sync with the MEM user TZ times.
(So, not as I said before, needing the user to tell us their OS/browser TZ.)
Q: Isn't all data stored in UTC and transformed at render-time by passing through user-time?
A: There's MEM render time for all the HTML dates, and browser JS render time for the CG selection overlay.

Josh Sled writes: 
revno: 7387
revision-id: jsled@asynchronous.org-20090721200535-4mevimqnpjfz56w1
parent: jsled@asynchronous.org-20090721184741-s52c2p5td5az3xvi
committer: Josh Sled <jsled@asynchronous.org>
branch nick: local
timestamp: Tue 2009-07-21 16:05:35 -0400
message:
  EM-3405, Bug#45858: Offset the browser-rendered time by the difference of the server user tz offset and the browser/os tz offset, such that it will match the string(s) generated on the server.

Marcos Palacios writes: 
Available in Build 2.1.0.1080.

Bill Weber writes: 
verified fixed in build 2.1.0.1080

Bill Weber writes: 
reopening since still has time issue with message when no queries are found on quan tab:

"No queries found between 7/23/09 2:59 PM and 7/23/09 3:08 PM"

An entry was added to the 2.1.0 changelog:

If the system time zone was different from that set in the Enterprise Dashboard, then there was inconsistency in the way times were displayed on the Query Analyzer page. Some times were displayed in the time zone of the system and some were displayed using the Dashboard time zone setting.