Please Note This forum exists for community support for the Mango product family and the Radix IoT Platform. Although Radix IoT employees participate in this forum from time to time, there is no guarantee of a response to anything posted here, nor can Radix IoT, LLC guarantee the accuracy of any information expressed or conveyed. Specific project questions from customers with active support contracts are asked to send requests to support@radixiot.com.
Excel Report TimeZone Behavior under Mango 3.7.x
-
Greetings, all:
Here's something exciting. One of our servers is set to Denver time. Some of our Customers are on the East Coast.
They run reports, using rollup on days and months. Their Time Zone is set for EST (since, well, they're on EST).
So. When they set up their report to do a Monthly Max or Delta, and run it in Denver (SERVER TIME), it runs swell. When they run a report using their own local timezone (EST, in this case) the rollup appears to run the report on the MST (SERVER timezone) settings.
This causes confusion, since the "monthly" reports are actually last day or previous month to day before the last day of this month, due to the 2-hour timeshift, and the midnight hour.
So:
Here's what we see:
Our reports (running on a user with EST Time Zone)
has this line:
"TS_Start - 2020/07/31 22:00:00 MDT to 2020/08/31 22:00:00 MDT"
when set for "monthly" rollups.Their TimeZone is set to EST, but the reports adjust to MDT to run the report.
If we set their Timezone to Mountain (or server time zone) the following line comes out:
"TS_Start - 2020/08/01 00:00:00 MDT to 2020/09/01 00:00:00 MDT"Which is what I expect to see. Notice in the previous line, the start and end are 7/31 to 8/31, when we really want 8/1 to 9/1.
I guess what I expect to see, when a report is ran by a user with EST Time Zone Settings, would be this:
"TS_Start - 2020/08/01 00:00:00 EST to 2020/09/01 00:00:00 EST"
Which bit of obviousness am I missing here?
-
Hi Greg
Are these reports using a relative time period or a specific?
Are the reports scheduled to run or does the use run them himself? -
The reports use 'relative' time periods and are set to 'Previous' 1 month + they are set to run on the first of every month at 6 am using the cron pattern (0 0 6 1 * ?).
Additionally, the reports that came in this morning are still suffering from this problem BUT when I run the reports manually the problem no longer persists.
-
Greetings:
Just to add:
ItsPronouncedDAYtuh and I are working together on this thing:We've both been able to replicate this:
(1) If you run a report by Config-> Run, it runs with the local timezone of the user.
(2) If you run a report automatically, it appears to run the report based on adjusting the offset to server time as described above based on the last time the user saved the report. So, for example, if you switch your user account timezone, and the report runs next, it does NOT take the new timezone settings. The user has to go back to the report, re-save the report with their new timezone settings, and then the report runs in that timezone as described above.In all cases, it appears as though the report is running based on Server Time, but not properly adjusting for the users' time zone setting...
To replicate this:
(1) Set up a report.
(2) Run the report
(3) Change your timezone away from the server timezone
(4) Run the report again
(5) You'll see that the From and Too have been adjusted to the SERVER timezone, with offsets applied from the User timezone, However, something is goofy in this, and it turns out that the data is actually read based on the User TZ, causing a real offset in the data: If a User is in Eastern and runs a report on a Server in Mountain, the report does run over the last month, but it does it as if the user configured the report to run from an offset defined by the difference between user TZ and Server TZ.This results in, for example, a "last month" setting on relative time to run really from 7/31-8/30, rather than 8/1 to 8/31 (for example).
Cheers,
-Greg Linder -
@turbo said in Excel Report TimeZone Behavior under Mango 3.7.x:
2020/07/31 22:00:00 MDT
In my opinion, the report is always running on server time and the timestamps are corrected to the users timezone.
ie: the report is always going from 2020/08/01 00:00:00 MDT to 2020/09/01 00:00:00 MDT
When the user that has been set to EST views those time stamps they are corrected to:
2020/07/31 22:00:00 EST to 2020/08/31 22:00:00 EST -
@craigweb said in Excel Report TimeZone Behavior under Mango 3.7.x:
@turbo said in Excel Report TimeZone Behavior under Mango 3.7.x:
2020/07/31 22:00:00 MDT
In my opinion, the report is always running on server time and the timestamps are corrected to the users timezone.
ie: the report is always going from 2020/08/01 00:00:00 MDT to 2020/09/01 00:00:00 MDT
When the user that has been set to EST views those time stamps they are corrected to:
2020/07/31 22:00:00 EST to 2020/08/31 22:00:00 ESTSo if the time stamps are being 'corrected' to the server time, the time stamps should read 2020/08/01 02:00:00 EST to 2020/09/01 02:00:00 EST- but instead the times are just being moved two hours prior to the server time (when in actuality it is two hours in the other direction).
What is happening is not so much a correction, as it is an incorrect adjustment.
e: minor phrasing correction
-
Looks like I got confused with the timezones, not familiar with US timezones.
"What is happening is not so much a correction, as it is an incorrect adjustment" I agree with this, seems like it is rendering the timestamp incorrectly as the timestamp doesn't change.
-
Greetings:
This just came up again with one of my customers- The Time Zone changed, and things are a little broken again. Is this something that can be fixed so that the not incorrect improper correctional timezone adjustment adjusting is adjusted so that the reports run based on the USERS TZ Selection (and not on the Server's TZ with some adjustment applied?).
Cheers,
-Greg -
To restate above:
My User has a THING in EDT that is monitored. The Mango Server lives in MST. The USER wants to see data on their site in their timezone, so they put in 8/1 to 8/31, they want to see data from 8/1 to 8/31 IN THEIR TIMEZONE (as set by the user timezone setting). They do not want to see that data read in at a different timezone and adjusted, because is in my example earlier, they want 8/1 to 8/31 in EST/EDT, and are getting 7/31-8/30, because "month" changes to midnight.
Any feedback here on this? I really hate daylight savings time, as it provides unnecessary headache to all of this.
What I just told my customer is "You'll have to set your TZ to MST, since that's the best way to make all this line up"
Am I using this wrong? Which bit am I missing?
-
You are correct. Set each user in their respective timezones. Mango system will continue to use system timezone unless explicitly specified in the env.properties file.
I half wonder if your mango system should run GMT then all offsets are based from a zero offset per user rather than an offset of an offset..Fox
