best strategies for datetime handling in datasource and grid

6 posts, 0 answers
  1. erwin
    erwin avatar
    358 posts
    Member since:
    Dec 2006

    Posted 11 Nov 2012 Link to this post

    I'm very new to kendo ui, searched the forum but could not find a definitive answer.

    I have several grids that have to show DateTime values in the following Format:

    2012-10-07 16:16:35   (YYYY-MM-DD hh:mm:ss)

    The date/time should be editable, sortable and filterable in the grid.

    The value should be passed through json datasource to/from the server (I have control over both the client and the server part).

    So far, if I declare a field as "date" in the datasource, the filter works correctly but I loose the time part of the information in the grid.
    If I use string then I cannot filter correctly.

    Server side code preferably in python - or an example of a working JSON string would be nice.
    Maybe also an example that does use date AND time in the demos.

    Regards
    Erwin
  2. erwin
    erwin avatar
    358 posts
    Member since:
    Dec 2006

    Posted 12 Nov 2012 Link to this post

    OK, after a couple of hours searching the forums - as always, you find better results when you know what you are searching for. In my case the magic keywords are datasource schema parse and Parameter Map.

    DateTime handling seems to be really hard, and the docs are not quite helpful. What's needed is a CRUD grid example with datetime columns, DateTimePicker editors etc.

    What I found so far:

    Server Side (Python)
    class DateEncoder(json.JSONEncoder):
        """ Encoder class to override the default datetime rendering
        """
        def default(self, obj):
            if isinstance(obj, datetime.datetime):
                return obj.strftime('%Y-%m-%dT%H:%M:%S')
            else:
                return json.JSONEncoder.default(self, obj)
     
    # return json with custom rendered dates (q is our dictionary)
       web.header('Content-Type', 'application/json')
       return( json.dumps(q,cls=DateEncoder) )
    Kendo: use datasource parse to convert json to Javascript Date Objects

    schema:{
         parse: function(response) {
             $.each(response,function(idx,elem) {
                 if(elem.created && typeof elem.created=="string") {
                     elem.created = kendo.parseDate(elem.created,"yyyy-MM-ddTHH:mm:ss");
                 }
             });
             return response
         },
     
         model:{
             id:"id",
             fields:{
                 id:{
                     type:"number",
                     editable:false
                 },
                 name:{
                     type:"string"
                 },
                 created:{
                     type:"date",
                     editable:false
                 },
                 created_by:{
                     type:"string",
                     editable:false
                 }
             }
         }
     },
    Use transport parameterMap to serialize javascript Date to something the server understands

    dataSource:{
           
    type:"json",
            transport:{
                read:"Releases",
                destroy:{
                    url:"Releases/Destroy",
                    type:"POST"
                },
                create:{
                    url:"Releases/Create",
                    type:"POST"
                },
                update:{
                    url:"Releases/Update",
                    type:"POST"
                },
                parameterMap:function (options, operation) {
                    // if the current operation is an update
                    if (operation === "update" || operation === "create") {
                        //
                        // need to create serializable strings for the Date objects
                        //
                        var d = new Date(options.start);
                        options.start = kendo.toString(new Date(d), "yyyy-MM-ddTHH:mm:ss");
                        var d = new Date(options.end);
                        options.end = kendo.toString(new Date(d), "yyyy-MM-ddTHH:mm:ss");
                    }
                    // ALWAYS return options
                    return options;
                }
            },
    In the grid columns definition use a format string to display the datetime and specify a custom editor

    columns: [   
          {
            field:"environment",
            title:"Env"
        },
     
        {
            field:"name",
            title:"Name"
        },
        {
            field:"created",
            title:"Created",
            format: "{0:yyyy-MM-dd hh:mm:ss}",
               editor:dateTimeEditor
        },
        {
            field:"created_by",
            title:"Created By"
        },
        {
            command:"destroy"
        }
     
    ]
    finally use the kendoDateTimePicker as your custom editor:
    function dateTimeEditor(container, options) {
        $('<input name="' + options.field + '"/>')
                .appendTo(container)
                .kendoDateTimePicker({
                    format:"yyyy-MM-dd HH:mm:ss",
                    timeFormat:"HH:mm:ss"
                });
    }

    Rather complicated, but seems to work - or am I missing something here?
    Maybe somebody comes up with a more elegant solution.

    One thing that I didn't realize at first, was that the Create and Update functions should return the modified record according to the schema. For example, if you use database generated keys, or maintain a created timestamp through a database trigger, you can return this information as json result of the Create call.
    Then there is no need to manually refresh the grid (as is suggested in some posts).

    Now the next challenge: When a user filters a date using the date picker, the filter should find all rows with the same date and time from 00:00:00 to 23:59:59.

    regards
    Erwin
  3. Kendo UI is VS 2017 Ready
  4. Aaron Jessen
    Aaron Jessen avatar
    14 posts
    Member since:
    Feb 2009

    Posted 08 Jan 2015 in reply to erwin Link to this post

    Hi Erwin (or anyone else who knows),

    Thank you for posting your very helpful code. I know it's been a very long time since you posted about this, but I, too, am trying to filter rows on a given date, where the records' times can fall anywhere between 0:00:00 and 23:59:59. Currently, searching for 01/8/2015 does not return a record with the date/time of 01/8/2015 2:30:00 (the row can only be found with a 'greater than' filter, instead of 'equal to'). Do you, or anyone else, know how to accomplish this?

    Thanks again,

    Aaron Jessen
  5. Alexander Valchev
    Admin
    Alexander Valchev avatar
    2875 posts

    Posted 13 Jan 2015 Link to this post

    Hello guys,

    @Aaron do you actually need the time in the Grid data? If not, the easiest solution would be to trim the time information from the date object using custom parse function.

    schema: {
      model: {
        fields: {
          your_date_field: {
            type: "date",
            parse: function(data) { /* remove the time and return the parsed JS Date object */ }
          }
        }
      }
    }


    I hope this approach will fit in your scenario.

    Regards,
    Alexander Valchev
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
  6. Aaron Jessen
    Aaron Jessen avatar
    14 posts
    Member since:
    Feb 2009

    Posted 13 Jan 2015 in reply to Alexander Valchev Link to this post

    Hi Alexander,

    Thanks for your reply. I actually do need the time information, as it is a CRM-style scenario with precise due dates/times of activities. Any ideas?

    Aaron
  7. Alexander Valchev
    Admin
    Alexander Valchev avatar
    2875 posts

    Posted 16 Jan 2015 Link to this post

    Hello Aaron,

    Please check this forum thread as it discusses the same subject:

    Regards,
    Alexander Valchev
    Telerik
     
    Join us on our journey to create the world's most complete HTML 5 UI Framework - download Kendo UI now!
     
Back to Top
Kendo UI is VS 2017 Ready