My dates are held as UTC in my database.
I get my dates from the server, which are returned in moment format and therefore need to be case to javascript. So far so good.
).subscribe((data) => { data.data.forEach((d) => { d.clientCreatedDatetime = new Date(d.clientCreatedDatetime); d.creationTime = new Date(d.creationTime); });My grid is declared in the component ts file, here is the declaration for a datetime column:
{ field: 'clientCreatedDatetime', title: this.l('ClientCreatedDatetime'), _width: 100, minResizableWidth: 100, filter: 'date', filterable: true, hidden: false, columnMenu: true, format: '{0:dd/MM HH:mm}' }
I want to get all items since midnight yesterday so I set my grid filter:
setGridSettings(): GridSettings { let midnightYesterday = new Date(moment().subtract(1, 'days').startOf('day').utc().toString()); return { state: { skip: 0, take: 500, // Initial filter descriptor filter: { logic: 'and', filters: [{ field: 'clientCreatedDatetime', operator: 'gte', value: midnightYesterday }] }, group: [{ field: 'ncAction', aggregates: this.aggregates }], sort: [] },My grid shows the date filter perfectly: 18/05 00:00
I hit the filter button and expect the date to be passed to the server using utc which would be 17/05 23:00.
However, the value for the filter in the datasource request (I am using C# MVC) has not been converted to UTC.
I think, ok I will put in a filter extension and offset with the server timezone (which is not ideal):
private DateTime? GetFrom(IList<IFilterDescriptor> filters, string descriptorMember){ if (filters.Any()) { foreach (var filter in filters) { var descriptor = filter as FilterDescriptor; if (descriptor != null && descriptor.Member == descriptorMember && (descriptor.Operator.ToString() == "IsGreaterThanOrEqualTo" || descriptor.Operator.ToString() == "IsGreaterThan")) { return DateTimeOffset.Parse(descriptor.Value.ToString()).UtcDateTime; } else if (filter is CompositeFilterDescriptor) { GetFrom(((CompositeFilterDescriptor)filter).FilterDescriptors, descriptorMember); } } } return null;}This gets me the correct data from the server but when it is returned to the client the filter is reapplied and misses the first hour's worther of data.
How do I apply a filter in utc - the server should receive the filtered date in utc, the client should display the date in local time.
8 Answers, 1 is accepted
Rank 1
Veteran
Hi Bob,
Thank you for the provided details and feedback.
About the Dates filtering, by default all Kendo Ui for Angular Date Input components select a date in UTC with time and a time zone equal to the one used by the browser at the moment of the selection. This is valid for the Date filter, which uses a DatePicker, as well. Please check the following example:
https://stackblitz.com/edit/angular-easzn6?file=app/app.component.ts
and inspect the browser console after filtering per the "FirstOrderedOn" column:
On our side the time is based on our current time zone EEST. On your side that will be different unless your machine is configured to be in the same time zone. I am not sure what is the specific configuration on your side, but it seems that the Date is converted somewhere along the way as by default it is selected in a UTC format as demonstrated in the example and screenshot above. The DatePicler used by the filter returns a date in the same format as what would the new Date() constructor return.
The following line raises some uncertainty as well:
let midnightYesterday = new Date(moment().subtract(1, 'days').startOf('day').utc().toString());What else makes an impression is that you are using moment at some point in order to format the dates:
"My dates are held as UTC in my database.
I get my dates from the server, which are returned in moment format and therefore need to be case to javascript."
But it isn't clear why is the moment formatting required if the server stores the dates as UTC. It seems that the dates format is changed from UTC to something else at some point which causes the undesired behavior, but that isn't performed by the Kendo Grid.
In order to narrow down the root of the issue could you clarify a bit more what is the format of the received dates from the server on the client and what is the expected format that should be sent to the server. Thank you.
About the forum feedback, thank you for providing it, I can further assure you that each time we receive forum related feedback we forward it to the team that maintains the forums. Thank you once again for providing it and feel free to write back in case any additional details or assistance is required for this case.
Regards,
Svetlin
Progress Telerik
Our thoughts here at Progress are with those affected by the outbreak.
Rank 1
Veteran
Hi Svetlin,
Thanks very much for the detailed reply but I am still struggling to understand how filtering is applied to the grid after the result set is returned from the server.
I change my filter code to remove moment which I know you guys hate:
setGridSettings(): GridSettings { //let midnightYesterday = new Date(moment().subtract(1, 'days').startOf('day').toString()); const today = new Date() let yesterday = today.setDate(today.getDate() - 1) let midnight = new Date(yesterday).setHours(0,0,0,0,); let midnightYesterday = new Date(midnight); return { state: { skip: 0, take: 500, // Initial filter descriptor filter: { logic: 'and', filters: [{ field: 'clientCreatedDatetime', operator: 'gte', value: midnightYesterday }] }, group: [{ field: 'ncAction', aggregates: this.aggregates }], sort: [] }, columnsConfig: [{
On the grid the filter is displayed in local time (midnight yesterday) but when the request is received at the server it has not been translated back to UTC, thus I have a value of {21/05/2020 00:00:00} when it should be {20/05/2020 23:00:00}.
Now, I can apply the method outlined in my first post to capture the filter descriptor and carry out a datetimeoffset to UTC. That gives me the correct values from the database.
But once the correct values from the database the result set must be cast back to a DataSourceResult: (await query.ToDataSourceResultAsync(request)). This then applies the filter set to {21/05/2020 00:00:00} thus missing all records that fall into the datetime offset.
I'm sure there is a simply way to get this working but I seem to be missing some fundamentals. Can you advise?
With regard to the state of this forum I would like to point you to github where we can format code, copy and paste images and use markup to get a point across. In terms of making an immediate fix why is that you can paste images to support your point but I can't?
Hi Bob,
Thank you for the provided code snippet.
I am afraid that I didn't understand the case properly, but it seems that the filter descriptors need to be manually adjusted before they are sent to the server and also the request on the server needs to be modified as well. Let me provide some more details.
Generally speaking, the Grid relies on emitting events when the data is changed - filterChange, sortChange and etc. The events contain important information about the manipulation the user is trying to achieve (such as FilterDescriptor, SortDescriptor etc). As a result, the Grid is filtered, sorted, and etc. The dataStateChange combines all these separate events and fires after each of them. This allows the developer to manipulate the data in a single instead of many event handlers.
The developer can customize the incoming filtering descriptors date (the filter property of the State object, available as event data in the dataStateChange event), by converting the dates in an appropriate format before sending the state to the server. Then in the request object ensure the format of the date received in the server.
Another option is to modify the request object itself in the server (request.Filter... ) before invoking the ToDataSourceResult method with the modified request (ToDataSourceResult(modifiedRequest)).
I hope this helps. Let us know if any further questions come up on this case.
Regards,
Martin
Progress Telerik
Our thoughts here at Progress are with those affected by the outbreak.
Rank 1
Veteran
Hi Martin,
"The developer can customize the incoming filtering descriptors date (the filter property of the State object, available as event data in the dataStateChange event), by converting the dates in an appropriate format before sending the state to the server. Then in the request object ensure the format of the date received in the server."
Can you explain what I should be doing in the angular client to ensure the date values in the datasourcerequest remain in utc format?
Currently on the server what I am having to do is implement a filter modifier:
private void ModifyFilters(IList<IFilterDescriptor> filters) { if (filters.Any()) { foreach (var filter in filters) { var descriptor = filter as FilterDescriptor; if (descriptor != null && DateTime.TryParse(descriptor.Value.ToString(), out DateTime Temp) == true) { descriptor.Value = DateTime.Parse(descriptor.Value.ToString()).ToUniversalTime();... code removed for brevity
This is hardly ideal. The grid and filter know the javascript value is utc and offsets. And then when it passes it to the server it says "that utc object you passed me is now in locatime, figure out how to put it back to utc before you apply it".
Hardly ideal.
Hi Bob,
Thank you for the provided code snippet.
In order to avoid any potential misunderstanding, I will summarize the desired behavior and the options that can be used to achieve it.
Based on that: "How do I apply a filter in utc - the server should receive the filtered date in utc, the client should display the date in local time." the developer has full control over the dates format.
In order to store the dates in UTC format in the server there are a few options:
1. The first option is when the filterChange is emitted, in the event handler the developer can modify the FilterDescriptor value. The same is valid when dataStateChange event is emitted.
https://stackblitz.com/edit/angular-eb6yvh?file=app/app.component.ts
2. The second option is to modify the date format in the service, before sending it to the server.
In order to convert the UTC dates received from the server to local time (before applying it to the Grid), the developer should modify manually the dates and update the value property of the FitlerDescriptor with the updated date format.
If I am missing something please let me know.
Regards,
Martin
Progress Telerik
Our thoughts here at Progress are with those affected by the outbreak.
Rank 2
Veteran
Iron
Hi Martin,
Two months later and still nowhere near a resolution.
All I want is some working code showing me a grid filter in localtime and how to ensure that local time is converted to utc before being set to the server. At the moment the dates are being displayed as you would expect, in local time. When the values are sent to the server they remain in local time. For example I have an audit log grid where I initially set the date filter to one hour ago;
let oneHourAgo = new Date(moment().subtract(1, 'hours').toString());return { state: { skip: 0, take: 100, // Initial filter descriptor filter: { logic: 'and', filters: [{ field: 'executionTime', operator: 'gte', value: oneHourAgo }] }, group: [], sort: [] },Having set the filter I get the data using a kendogrid service. The filter is set to localtime and therefore, because I am one hour ahead of utc, nothing is returned to the grid. At what stage, where and how do I ensure times displayed as local in the browser are converted before being set back to the server?
Hi Bob,
Going through the history of this ticket, it seems that you understand how dates function but are trying to find another approach of achieving a specific requirement.
"This is hardly ideal. The grid and filter know the javascript value is utc and offsets. And then when it passes it to the server it says "that utc object you passed me is now in locatime, figure out how to put it back to utc before you apply it".
Hardly ideal."
Indeed I can confirm that this behavior is valid and expected. Let me summarize the concept when using dates. The selected date of any Kendo UI for Angular component is provided in UTC with the local browser time offset. Once a date is selected it is up to the developer to decide what to do with this date as there are multiple different scenarios available for handling dates. For example the date can be kept the same, or its time part can be stripped off, or the time can be adjusted manually by one hour as demonstrated in your last reply, or any other manipulation.
"At what stage, where and how do I ensure times displayed as local in the browser are converted before being set back to the server?"
Any required date conversion should be performed right before the request is sent to the server while keeping the dates used in the client untouched. However, we cannot answer on the how part as that can be achieved in multiple different ways. What else could be done is to send the dates as they are (unchanged) to the server and on the server to perform some manipulation in order to convert the dates to the required format as required for the particular data base or server.
Also, if the dates coming from the server to the client aren't JavaScript Dates then these should be converted to be valid JavaScript Dates so that the Grid can filter them as dates. That can be performed either at the server or on the client right after receiving a successful response of the request on the client.
Regards,
Svetlin
Progress Telerik