How can i get an import package to display dates and times? right now they say object Object?

How can i get an import package to display dates and times? right now they say object Object?

bkuhnbkuhn Posts: 11Questions: 1Answers: 0

(```)

<

    <table id="example" class="table table-bordered table-striped">
                <thead >
                  <tr>
                   <th >Service Date</th>
                  <th >Route</th> 
                 <th >Bus</th>  
                   <th >Leave Yard</th>
                    <th >Leave OD</th> 
                    <th >inservice</th>
                    <th> InServe OD</th> 
                     <th >Out of Service</th> 
                    <th >OutServe OD</th>
                    <th >Return to Yard</th>            
                     <th >ReturnYard OD</th>
                    <th >leave for break</th>   
                     <th >Return From Break</th>
                    <th >leave for break2</th>  
                     <th >Return From Break2</th>

                </tr>
                </thead>                


   </div>

</div>

    var editor; // use a global for the submit and return data rendering in the examples

    function selectColumns ( editor, csv, header ) {
        var selectEditor = new $.fn.dataTable.Editor();
        var fields = editor.order();

        for ( var i=0 ; i<fields.length ; i++ ) {
            var field = editor.field( fields[i] );

            selectEditor.add( {
                label: field.label(),
                name: field.name(),
                type: 'select',
                options: header,
                def: header[i]
            } );
        }

        selectEditor.create({
            title: 'Map CSV fields',
            buttons: 'Import '+csv.length+' records',
            message: 'Select the CSV column you want to use the data from for each field.',
            onComplete: 'none'
        });

        selectEditor.on('submitComplete', function (e, json, data, action) {
            // Use the host Editor instance to show a multi-row create form allowing the user to submit the data.
            editor.create( csv.length, {
                title: 'Confirm import',
                buttons: 'Submit',
                message: 'Click the <i>Submit</i> button to confirm the import of '+csv.length+' rows of data. Optionally, override the value for a field to set a common value by clicking on the field below.'
            } );

            for ( var i=0 ; i<fields.length ; i++ ) {
                var field = editor.field( fields[i] );
                var mapped = data[ field.name() ];

                for ( var j=0 ; j<csv.length ; j++ ) {
                    field.multiSet( j, csv[j][mapped] );
                }
            }
        } );
    }


    $(document).ready(function() {
        editor = new $.fn.dataTable.Editor( {
           ajax: '../php/dates.php?format=custom',
            table: "#example",      
            ajax: {url :"addDRImport.php", type: "POST"},

            fields: [ {

                    label: "service_date:",
                    name: "service_date"
                                    }, {
                    label: "Route:",
                    name: "Route"
                },{
                    label: "bus:",
                    name: "bus"
                },{
                    label: "leave_yard:",
                    name: "leave_yard"
                         }, {   
                    label: "Lvyd_odometer:",
                    name: "Lvyd_odometer"
                            }, {
                    label: "inservice:",
                    name: "inservice"
                }, {
                    label: "inserv_odometer:",
                    name: "inserv_odometer"
                }, {
                    label: "out_of_service:",
                    name: "out_of_service"
                }, {
                    label: "outserv_odometer:",
                    name: "outserv_odometer"
                }, {
                    label: "return_to_yard:",
                    name: "return_to_yard"
                }, {    
                    label: "rtnyd_odometer:",
                    name: "rtnyd_odometer"
                }, {            
                    label: "leave_for_break:",
                    name: "leave_for_break"
                }, {            
                    label: "return_from_break:",
                    name: "return_from_break"
                }, {            
                    label: "leave_for_break2:",
                    name: "leave_for_break2"
                            }, {            
                    label: "return_from_break2:",
                    name: "return_from_break2"

                }
            ]
        } );

        var uploadEditor = new $.fn.dataTable.Editor( {
            fields: [ {
                label: 'CSV file:',
                name: 'csv',
                type: 'upload',
                ajax: function ( files, done ) {
                    // Ajax override of the upload so we can handle the file locally. Here we use Papa
                    // to parse the CSV.
                    Papa.parse(files[0], {
                        header: true,
                        skipEmptyLines: true,
                        complete: function (results) {
                            if ( results.errors.length ) {
                                console.log( results );
                                uploadEditor.field('csv').error( 'CSV parsing error: '+ results.errors[0].message );
                            }
                            else {
                                selectColumns( editor, results.data, results.meta.fields );
                            }

                            // Tell Editor the upload is complete - the array is a list of file
                            // id's, which the value of doesn't matter in this case.
                            done([0]);
                        }
                    });
                }
            } ]
        } );

        var table = $('#example').DataTable( {
             ajax: '../php/dates.php?format=custom',
            ajax: 'data/objects_subarrays.txt',
            dom: "Bfrtip",      
            ajax: {url :"viewDRImport.php", type: "POST"},

            processing: true,
            serverSide: true,
            columns: [
                { data: "service_date" },
                { data: "Route" },
                { data: "bus" },
                { data: "leave_yard" },
                { data: "Lvyd_odometer" },
                { data: "inservice" },
                { data: "inserv_odometer" },
                { data: "out_of_service" },
                { data: "outserv_odometer" },
                { data: "return_to_yard" },
                { data: "rtnyd_odometer" },
                { data: "leave_for_break" },
                { data: "return_from_break" },
                { data: "leave_for_break2" },
                { data: "return_from_break2" }
            ],
            select: {
                style: 'os',
                selector: 'td:first-child'
            },
            buttons: [ 
                {
                    extend: 'csv',
                    text: 'Export CSV',
                    className: 'btn-space',
                    exportOptions: {
                        orthogonal: null
                    }
                },
                {
                    text: 'Import CSV',
                    action: function () {
                        uploadEditor.create( {
                            title: 'CSV file import'
                        } );
                    }
                },

                {
                    extend: 'print',
                    text: 'Print',
                    exportOptions: {
                        modifier: {
                            page: 'current'
                        }
                    }
                }
            ]
        } );    
    } );
</script>

(```)

This question has an accepted answers - jump to answer

Answers

  • allanallan Posts: 61,972Questions: 1Answers: 10,160 Site admin

    Can you show me the data that is being loaded (i.e. the JSON) and also a screenshot of the result please?

    Thanks,
    Allan

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    Without seeing an example of your data its hard to offer suggestions. Providing a simple test case with an example of your data will help us to help you.
    https://www.datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

    I see you have server side processing enabled. Use the browser's network inspector to get a page of data to use in the test case. Use the data option to apply the row data to the Datatable. Similar to this example. You will only need to copy the columns config into the test case and provide the HTML table.

    Kevin

  • bkuhnbkuhn Posts: 11Questions: 1Answers: 0

    I am not sure how to use the example page. I loaded the php file and the data into this page

    https://live.datatables.net/bipadisi/1/

  • bkuhnbkuhn Posts: 11Questions: 1Answers: 0
    edited March 29

    Here is a screen shot of the page

    On my other datatables these items that say object would have a time or a date in them, but when using the import function, they say object OBJECT?

    This is a City that receives Federal Funding , so i cannot link to their page and the data is coming from SQL so I cannot get the datatables page to load a sample

  • colincolin Posts: 15,177Questions: 1Answers: 2,590

    As Allan said, it would be helpful to see the data that gets loaded into the table. Could you post a couple of lines of the JSON here, so we can see what DataTables receives from the server, please.

    Colin

  • bkuhnbkuhn Posts: 11Questions: 1Answers: 0

    In addition to the SSFRImport.php file, I have a ViewFRImport.php file.
    https://live.datatables.net/muxoyeho/2/

  • bkuhnbkuhn Posts: 11Questions: 1Answers: 0

    SELECT top 10 [ID]
    ,[service_date]
    ,[Shift]
    ,[bus]
    ,[leave_yard]
    ,[Lvyd_odometer]
    ,[inservice]
    ,[inserv_odometer]

      ,[out_of_service]
    

    ,[outserv_odometer]
    ,[return_to_yard]

    ,[rtnyd_odometer]

      from FixedRouteRecords
    

    Returns
    ID service_date Shift bus leave_yard Lvyd_odometer inservice inserv_odometer out_of_service outserv_odometer return_to_yard rtnyd_odometer
    30463 2024-03-27 40MF 80 04:38:00 255967 05:02:00 255970 19:54:00 256229 20:15:00 256231
    30464 2024-03-27 41MF 78 04:40:00 238517 05:03:00 238523 19:48:00 238765 20:02:00 238771
    30465 2024-03-27 42MF 55 04:40:00 358144 05:00:00 358149 19:51:00 358365 20:02:00 358370
    30466 2024-03-27 43MF 54 04:39:00 389658 05:03:00 389664 19:49:00 389843 20:01:00 389848
    30467 2024-03-27 44MF 79 04:40:00 184165 04:58:00 184163 19:50:00 184409 20:00:00 184411
    30454 2024-03-26 40MF 80 04:38:00 255704 05:05:00 255706 19:48:00 255965 19:56:00 255967
    30455 2024-03-26 41MF 74 04:40:00 83616 05:03:00 83622 11:14:00 83731 11:14:00 83731
    30456 2024-03-26 41MF 79 11:14:00 184010 11:14:00 184040 19:45:00 184151 19:59:00 184165
    30457 2024-03-26 42MF 73 04:38:00 87782 05:00:00 87787 12:55:00 87902 13:25:00 87906
    30458 2024-03-26 42MF 78 12:57:00 238412 13:00:00 238411 19:51:00 238512 20:11:00 238517

  • bkuhnbkuhn Posts: 11Questions: 1Answers: 0

    Sorry, I am not an IT guy

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    Use the browser's network inspector to see the JSON response. The steps are shown in this technote.

    Kevin

  • bkuhnbkuhn Posts: 11Questions: 1Answers: 0

    Is this what you need?
    {"draw":0,"recordsTotal":1,"recordsFiltered":1,"data":[{"service_date":{"date":"2024-03-27 00:00:00","timezone_type":3,"timezone":"America\/Los_Angeles"},"Shift":"40MF","bus":80,"leave_yard":{"date":"2024-03-29 04:38:00","timezone_type":3,"timezone":"America\/Los_Angeles"},"inservice":{"date":"2024-03-29 05:02:00","timezone_type":3,"timezone":"America\/Los_Angeles"},"out_of_service":{"date":"2024-03-29 19:54:00","timezone_type":3,"timezone":"America\/Los_Angeles"},"return_to_yard":{"date":"2024-03-29 20:15:00","timezone_type":3,"timezone":"America\/Los_Angeles"},"Lvyd_odometer":255967,"inserv_odometer":255970,"outserv_odometer":256229,"rtnyd_odometer":256231}]}

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794
    edited March 29

    Is this what you need?

    Yes :smile:

    The columns are defined like this:

                columns: [
                    { data: "service_date" },
                    { data: "Route" },
                    { data: "bus" },
                    { data: "leave_yard" },
                    { data: "Lvyd_odometer" },
                    { data: "inservice" },
                    { data: "inserv_odometer" },
                    { data: "out_of_service" },
                    { data: "outserv_odometer" },
                    { data: "return_to_yard" },
                    { data: "rtnyd_odometer" },
                    { data: "leave_for_break" },
                    { data: "return_from_break" },
                    { data: "leave_for_break2" },
                    { data: "return_from_break2" }
                ],
    

    The JSON response has nested objects:

    {
        "draw": 0,
        "recordsTotal": 1,
        "recordsFiltered": 1,
        "data": [
            {
                "service_date": {
                    "date": "2024-03-27 00:00:00",
                    "timezone_type": 3,
                    "timezone": "America\/Los_Angeles"
                },
                "Shift": "40MF",
                "bus": 80,
                "leave_yard": {
                    "date": "2024-03-29 04:38:00",
                    "timezone_type": 3,
                    "timezone": "America\/Los_Angeles"
                },
                "inservice": {
                    "date": "2024-03-29 05:02:00",
                    "timezone_type": 3,
                    "timezone": "America\/Los_Angeles"
                },
                "out_of_service": {
                    "date": "2024-03-29 19:54:00",
                    "timezone_type": 3,
                    "timezone": "America\/Los_Angeles"
                },
                "return_to_yard": {
                    "date": "2024-03-29 20:15:00",
                    "timezone_type": 3,
                    "timezone": "America\/Los_Angeles"
                },
                "Lvyd_odometer": 255967,
                "inserv_odometer": 255970,
                "outserv_odometer": 256229,
                "rtnyd_odometer": 256231
            }
        ]
    }
    

    This example shows how to display nested objects. The columns will need to be defined like this:

                columns: [
                    { data: "service_date.date" },
    ....
    

    Kevin

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    Also note that the draw value is 0 in your snippet. You may have edited when pasting but the value should never be 0. It needs to match the draw value in the request. I assume this is correct in your environment as the Datatable is displaying rows.

    Kevin

  • bkuhnbkuhn Posts: 11Questions: 1Answers: 0

    First Thank you soo much. I have been researching the datatable manuals for two eeks now. this is very close thanks to you and I really appreciate it.
    That is bringing the date back now, but the times are in the database as 07:00 with no date. The datatable is trying to add todays date to last years entries. is there a way to show time only for the time fields?

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    but the times are in the database as 07:00 with no date

    The client side Datatable doesn't modify the supplied data. The server script is returning the data as 2024-03-27 00:00:0 with the date and time. If this is not expected then it suggests that there is a disconnect between the server data query (how its handling datetime formats) and what is stored in the DB. The server script will need to be investigated.

    Kevin

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794

    What is the data type for those time fields in the database?

    Kevin

  • bkuhnbkuhn Posts: 11Questions: 1Answers: 0

    We have dozens of datatables that properly bring back the time, but we only have this one import datatable and the times in the db do not have dates only times. But the datatable is displaying todays date and the time from the db on each entry. The service_date field is correctly bringing back the db date, but the time in the same row will show the current date with the time from the db. Is there a format function to add %H:%M:%S?

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794
    edited March 29

    Maybe a renderer will do what you want. See these examples. Another option, if the datetime render doesn't fit your needs, is to use columns.render with a datetime library, ie moment.js, to display in the format you want. See this example.

    Kevin

  • bkuhnbkuhn Posts: 11Questions: 1Answers: 0

    If
    columns: [
    { data: "service_date.date" },
    ....
    is used to format the value of date, what do i use for the time only?
    columns: [
    { data: "leave_yard" },
    I have looked at the examples and tried everything shown, but none of these are for time only?

  • kthorngrenkthorngren Posts: 20,425Questions: 26Answers: 4,794
    Answer ✓

    Here is an example using moment.js.
    https://live.datatables.net/tefotoha/1/edit

    The example assumes the original datetime format looks like 2024-03-29 04:52:00. See the moment.js display format docs to learn how to format the date output.

    If you prefer you can use another datetime library like Luxon.

    Kevin

  • bkuhnbkuhn Posts: 11Questions: 1Answers: 0

    This worked. Thank you so much for sticking with this...
    data: "service_date.date",
    render: DataTable.render.datetime('M/DD/YYYY')},
    data: "leave_yard.date",
    render: DataTable.render.datetime('HH:mm:ss')},

Sign In or Register to comment.