Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 10 of 13 total
Thread Delphi JSON Time Error
Wed, Sep 19 2018 3:11 AMPermanent Link

Eivind

All

Suddenly when recompiling my project and testing some functionality I have not tested for a month or two I run into some serious problem with encoding a Time field from SQL Server Express. This has all worked perfectly in the past and I cannot put my finger on when this could have happened. Here is the issue:

I'm using EWBDataSetAdapter and EWBDataBaseAdapter in a Delphi ISAPI project. The problem occurs with Time fields in SQL Server that is being encoded with a negative value during a load. This is the cutout of the JSON string being generated by the DataSetAdapter:

"TIME_FROM": -2209161600000, "TIME_TO": -2209143600000

Negative values??

Whereas If I try to post data from the EWB app to my server the same fields looks like this:

"TIME_FROM": 0, "TIME_TO": 18000000

This is correct as TIME_FROM = 00:00 and TIME_TO = 05:00

Again, all this have worked like a charm before and now when I suddenly tried some functionality that I have not touched for while after implementing some new functionality it all went sour. I have checked that there is no problems with my database driver (UniDac) and logged the TIME_FROM and TIME_TO before the DataSetAdapter encodes it. This all looks good with TIME_FROM = 00:00 and TIME_TO = 05:00. In the database itself the data is also correct. All Date fields is working good as well, only time fields creates this problem

I'm now using the latest build 18 of both EWB and the modules and I have been updating as new builds come out. The only thing I can put my finger on is that I had a major Windows 10 update installed yesterday. However, I cannot tell if these problems occurred before this Windows patch or not.

I'm really in need of some assistance here as I cannot compile new versions to my customer that are waiting for them. Any help is highly appreciated .

Thanks

Eivind
Wed, Sep 19 2018 4:47 AMPermanent Link

Eivind

Ok, maybe I posted a bit too early. It looks like the negative values are correct? On Edge, Firefox and Safari it works ok, but not on Google Chrome where the time values are displayed completely wrong. Please see the attached pictures. One from Edge and one from Chrome. The same dataset are loaded and the one in Edge is correct. The one in Chrome has completely wrong from and too times. This should be a time diary from midnight to midnight.

What sucks is that I recommended my users to use Chrome as that has been the browser I have been tested with. However, the newest version messes all the time values up.



Attachments: Time Lists.rar
Wed, Sep 19 2018 11:04 AMPermanent Link

Eivind

Ohh well, why not answer my own question.... Smile

Something have definitely changed since my "old" code war written, but the most important thing is that I managed to solve it. After a lot of digging I manage to find info on "LocalizeDateTimeColumns" and enabled that for the dataset. That solved the listing of data in all browsers. (Why it was shown different on Chrome is still a pusle for me). Anyhow, I also did a lot of time calculations that previously worked perfectly, but failed miserable now. Again after a lot of trial and error I solved it in the end by using UTC = True in StrToTime and TimeToStr functions. (Something I did not needed before though for correct calculations).

So, fast forward after banging my head in the wall and pulling out the little hair I have, it had a happy ending. Luckily not all days are like this Smile

Br
Eivind
Wed, Sep 19 2018 1:01 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Eivind,

<< Ohh well, why not answer my own question.... Smile>>

Sorry, my attendance on the forums is a little spotty while I finish up EWB3 and some other stuff for customers.  If you run into something like this in the future, feel free to send me a direct email so that I can get you an answer quickly instead of making you wait.

<< Something have definitely changed since my "old" code war written, but the most important thing is that I managed to solve it. After a lot of digging I manage to find info on "LocalizeDateTimeColumns" and enabled that for the dataset. That solved the listing of data in all browsers. (Why it was shown different on Chrome is still a pusle for me). Anyhow, I also did a lot of time calculations that previously worked perfectly, but failed miserable now. Again after a lot of trial and error I solved it in the end by using UTC = True in StrToTime and TimeToStr functions. (Something I did not needed before though for correct calculations). >>

There have been a bunch of changes to dates/times in order to deal with issues with things like "what to do with a date that doesn't have a time portion".   The latest was this, which is what I think you were seeing:

https://www.elevatesoft.com/incident?action=viewaddr&category=ewb&release=2.06&incident=4669
https://www.elevatesoft.com/incident?action=viewaddr&category=ewb&release=2.06&incident=4670

The original incident report was:

https://www.elevatesoft.com/incident?action=viewaddr&category=ewb&release=2.06&incident=4655

Tim Young
Elevate Software
www.elevatesoft.com
Thu, Sep 20 2018 2:41 AMPermanent Link

Eivind

Tim

Thanks for your answer. There is still something I cannot get my head around when it comes to times. My server (Windows Server on Azure) and DB (SQL Server Azure) are all set to UTC timezone. I want times to be displayed equally on all clients regardless of their timezone settings on the client. Is this what the LocalizeDateTimeColumns settings are for? If I leave this OFF on both the EWB app and DataSetAdapter in my Delphi ISAPI dll I would presume this meant that times should be displayed without consideration of the users timezone. However, when a client runs the EWB app with the UTC timezone set, it shows the same and correct time as in the DB. However, when the user has a different timezone, the times are changed. Do I understand LocalizeDateTimeColumns property correct? Should it be set to False to NOT convert it from UTC to local time?

Also, If I leave LocalizeDateTimeColumns set to False, the times show correct on Safari and other browsers, but incorrect on Chrome and Opera. However on Safari the sorting does not work correctly on time columns.

I'm still back to having to sort on String time columns in the grid to overcome this.

Br
Eivind
Thu, Sep 20 2018 1:13 PMPermanent Link

Raul

Team Elevate Team Elevate

On 9/20/2018 2:41 AM, Eivind wrote:
> Thanks for your answer. There is still something I cannot get my head around when it comes to times.

Sorry for long post but this is my understanding of this in general

Please correct as needed.

- localization only applies to timestamps (i.e. date + time) since date
alone or time alone are not really localize-able (not enough info to
know what to do due to midnight issues and daylight savings changes).

- localization might have to take place on server and client since only
they know their local timezone (and do not know each others timezone)

- database table column stores just a timestamp. This could be UTC or
might be local - it's a design decision and should be consistent for you
(as a developer) and take into account what apps access this and how.

- (EWB) Web server would need a setting to handle both cases when
serving or saving this data to/from clients - basically whether it
should try to convert time from/to local or leave "as is"

- similarly on (EWB) app there needs to be a setting that when it
receives or submits timestamp what should it do (for example if incoming
timestamp is UTC it likely should present it in local and similarly when
user enters local time then it should convert to UTC before sending)

So depending on what server does and where clients are there are many
ways this "looks right" but might not be.


Enabling this conversation to/from is what the LocalizeDateTimeColumns
settings are for in the EWB dataset and the "Localise Date/Time Columns"
setting in the EWB Web Server dataset confg page.


Examples

1. user enters local -> ewb app sends "as is" -> "ewb server uses "as
is" -> in database the timestamp would be user local.

As long as all users are in same timezone it all should work but as soon
as some user is in some other timezone they would see wrong time

2. user enters local -> ewb app converts to UTC -> "ewb server uses "as
is" -> in database the timestamp would be in UTC now.

As long EWB web server returns it "as is" and ewb app localizes user
will see it right. However any user accessing table direct (ie.. edb
manager or other app that is nto time aware) would see UTC time which
could appear "wrong" to them (if they are in timezone other than utc)

3. user enters local -> ewb app converts to UTC -> "ewb server converts
local -> in database the timestamp would be in server local.

EWB web server needs to convert it to UTC when returning and ewb app
from UTC to local and any user will see it right. Any user accessing
table direct (edbmanager) would see it in server local now


Example 1 i would personally try to avoid though 2 and 3 should botrh
work depending on ones needs.


Raul
Fri, Sep 21 2018 3:03 AMPermanent Link

Uli Becker

Raul,

I dont'work with different timezones, but thanks a lot for this detailed and helpful post!

Uli
Fri, Sep 21 2018 4:05 AMPermanent Link

Matthew Jones

A good summary of the complications of time management in software. My strong recommendation for anything where time zones may come into it is to do everything in UTC. And if you aren't sure, use UTC anyway. In general, the database should store UTC always, and convert to local at the appropriate point for display.

But sometimes this doesn't work out - and you need to consider what you really want, and either store a time zone with the data, or some other indicator. One application I work on has a "local time" flag which indicates that a time is always local. Like the Ten O'clock News, doesn't matter which zone you are in, and particularly around daylight savings changes...

Which is why the default of UTC in the database, and localise as close to the user, is generally a good default position.

--

Matthew Jones
Fri, Sep 21 2018 11:29 AMPermanent Link

Eivind

Raul / Matthew

Thanks for your input regarding this matter. I do store all times for this specific time diary in UTC on the server (SQL Server on Azure and time(7) fields) and my webserver are also set to UTC. I do want the time value to be displayed in UTC in the EWB app as well and not formatted according the users computer timezone. I agree that pure time values should not be formatted for timezone and thats exactly what I'm trying to achieve. I have the LocalizeDateTimeColumns flag set to False on both the server and the client but still the time values are changed depending on the users timezone settings. Just to get it straight.... Thats the whole purpose of the LocalizeDateTimeColumns flag right? Set it to false for not changing the time from UTC?

Also I have the other issue mentioned that times get corrupted on Chrome and Opera, but thats hopefully something Tim will look into sooner rather than later SmilePlease...

So now my app works almost fine on most browsers except Chrome and Opera. By almost I mean the sorting in the grid on a time columns does not work when I display UTC time. To resolve this I have two calculated string fields showing the time values and that sorts fine. Ohh well, eventually I will get there I hope.

Br
Eivind
Sat, Sep 22 2018 12:29 PMPermanent Link

Tim Young [Elevate Software]

Elevate Software, Inc.

Avatar

Email timyoung@elevatesoft.com

Eivind,

<< So now my app works almost fine on most browsers except Chrome and Opera. By almost I mean the sorting in the grid on a time columns does not work when I display UTC time. >>

The issue there is this incident report:

https://www.elevatesoft.com/incident?action=viewaddr&category=ewb&release=2.06&incident=4655

which was rolled back because it caused this:

https://www.elevatesoft.com/incident?action=viewaddr&category=ewb&release=2.06&incident=4669

This issue was related to time values that have weird date portions that mess with the sorting of the time values as raw values in EWB.  It's only tangentially related to localization in the sense that EWB need can't strip out the date portion on the client without causing the second issue.

However, I think overall our primary issue is, like Raul indicates, that EWB is trying to localize date and time values (not date/time or timestamp values).  The problem is that it's very hard to control how the other "missing" portion of a date/time is dealt with, so it's a case of reaching too far on my part and ending up with a muddled mess that is hard to understand.

More broadly, I think that I need to step back and decide whether EWB should even attempt to localize date, time, or date/time values on the server side (EWB Web Server) at all.  This is a decision for EWB 3, but I'm running out of time and need to make a decision soon so that I can make the necessary changes in the EWB 3 database API.

So, in summary, the changes that may be coming include:

1) No more localization of independent dates or times, thus allowing for the stripping of date values and thus, a solution for your issue.

2) No more localization of dates, times, and date/times on the server side, thus allowing for pass-through of values and leaving it up to the client to perform all necessary localization.

Tim Young
Elevate Software
www.elevatesoft.com
Page 1 of 2Next Page »
Jump to Page:  1 2
Image