issues with the time zone

#1

I have an issue with the time zone setting, I have a Dashboard that writes DateTime values thru a stored procedure in a table in the DB, the value stored corresponds exactly to what has been selected (I mean the year, month, day, hours and minutes).

BUT when I read those values, thru a Datacube, without setting whatever Time hierarchy on the DateTime values in order to keep the hours and minutes values, the hours gets updated in order to match the time zone I’m in (GMT +2).

To be clear, I declare the “19th of May 2021 9 a.m.” thru a range date filter, it’s stored with those values in the table, but when I read it, thru the Datacube, I get the “19th of May 7 a.m.”. I guess it’s due to the Default Time Zone in the general settings.
But my question is: why is the Time Zone taken into account when reading the DateTime from the DB if nobody asks for it specifically, can’t this be optional?

Is there a way to get rid of this in a smart way, or do I have only one option: change the default time zone to be at GMT +0?

Because if I add hours to the parameter before passing it to the stored procedure, the values I will deal with in the DB will not reflect the values selected by the end user when read by whatever platform but Dundas. And most of all: during 6 months we are at GMT +1, and at GMT +2 during the other 6 months.

Any help is welcome.

#2

It’s worse than I thought, even changing the default time zone to UTC doesn’t solve the issue (and yes, I have restarted the IIS web site after changing the setting).
The MS SQL server is on a machine set to “UTC Brussels, Paris” , the computer I access the data from is set to “UTC Brussels, Paris” too.
Why the hell is the date not read as it’s stored?? From other platforms, accessing the very same DB, the Datetime stored is the Datetime shown, no offset is applied.
What setting have I missed?

#3

to be more precise, the Datacube returns the correct hours, but when I try to show it on the Dashboard using a Range Date filter in order to be able to set/show the hours, the value is modified, as you can see on the screen capture below, the dataset returns the correct values, but the filter doesn’t.

image
image.png1261×382 18.1 KB

#4

Hi Olivier,

The date/time control always shows the browser’s time zone in Gregorian dates, however in this case, it looks that date is used as UTC by the server, since data has no time zone.

It would be great if we could troubleshoot this through email as I’d need some further information to be able to reproduce the issue or forward it to R&D team.

#5

The tests I have made make me say that the Range date filter shows only UTC + 00 values, and uses the time zone for modifying the value, in for removing the offset from the input value considered by default as being a value corresponding to the time zone set for the web site in order to show a UTC +00 value in the filter, out for adding the offset to the value set in the filter considering that it’s a UTC + 00 value.
A Datetime in MS SQL doesn’t take into account whatever time zone.

#6

What you mentioned is sort of correct.
I had an internal discussion with R&D team and here is the explanation on how the date/time filter works:

The date/time filter shows the dates in the browser’s local time. However, when you select the date, the date is converted to UTC and un-offset and then sent to the server to be used.

For example, if a user picks May 1, 2008 11pm and they are in the EST time-zone, it ends up as May 1, 2008 11pm UTC (and not May 2, 2008 3am UTC which is what EST->UTC would end up with). This way when the data comes back you see data for May 1, 2008 11pm. This is especially noticeable if you’re picking days since you’d select May 1 but see data for May 2 (if we didn’t do this conversion).

The server itself is time-zone agnostic. All times are assumed to be UTC (or really, no time-zone at all, but that ideology doesn’t exactly exist in JS).

So, whatever date+time you select in the date/time control ends up as the exact same date/time in UTC in the data request, and in the data retrieval. Time-zone is not taken into consideration at all except for display purposes in the browser.

To see the lack of time conversion happen in a browser (which is what would happen without our date picker), you can open Developers tool -> Console and type:

var testDate = new Date();

testDate;

> Fri May 21 2021 15:14:18 GMT-0400 (Eastern Daylight Time)

JSON.stringify(testDate);

> “2021-05-21T19:14:18.765Z”

As you can see if testDate was sent to the server as-is it would end up as +4 hours in UTC (the Z on the end of that string indicates zulu-time).

#7

Hi Azar,

When debugging the javascript I see the Datetime value in the Dataset and this value is “Mon May 24 2021 09:00:00 GMT+0200 (Central European Summer Time)”, BUT the filter shows “Mon May 24 2021 07:00:00”, and when setting the filter to “Mon May 24 2021 09:00:00” the filter returns “Mon May 24 2021 11:00:00 GMT+0200 (Central European Summer Time)”, but the value stored in the DB is “Mon May 24 2021 09:00:00” as the Datetime doesn’t take into account whatever time zone, therefore it takes only the UTC +00 value.
Why dealing with the Datetime as a UTC +00 in the filter? A Datetime data type in MS SQL doesn’t and will not deal with the time zone


it’s only the Datetimeoffset type that deals with the time zone:

Here I won’t be able to use this filter because of the offset that is applied when injecting the value thru code when reading it from the DB for showing it to the end-user in order to allow him/her to complete the rest of the form after he/she has set the Datetime value once.

Why can’t you consider, as the Datetime has no time zone info, that the value set thru code is a UTC +00? The output is not what is causing the trouble, it’s the interpretation of the input in the filter thru code.