http://live.datatables.net/gemotoca/1/edit?html,css,js,output

http://live.datatables.net/gemotoca/1/edit?html,css,js,output

etilleyetilley Posts: 31Questions: 4Answers: 0

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

This question has an accepted answers - jump to answer

Answers

  • etilleyetilley Posts: 31Questions: 4Answers: 0

    DateTime is returned by MySQL in seconds only. The Moment function does not recognize this format so how do we present date (only) in a datatable?

    Here is the test script ...

    http://live.datatables.net/gemotoca/1/edit?html,css,js,output

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    You can use columns.render to convert the date - see here,

    Colin

  • etilleyetilley Posts: 31Questions: 4Answers: 0

    It seems that the test case that I post doesn't save the date format MySQL is creating. What you should be seeing at the link.

        $(document).ready( function () {
          $.fn.dataTable.moment( 'DD/MM/YYYY HH:mm:ss' );
          var data = [
            ['1593388800000'],
            ['1593388800000'],
            ['1593388800000'],
          ];
    
            var table = $('#example').DataTable({
              data: data
            });
        } );
    
  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    The same principle applies - you'll just need to give the current moment format in the columns.render function. You can check their guide to get that,

    Colin

  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736

    Have you looked at this moment.js doc?
    https://momentjs.com/docs/#/parsing/unix-timestamp/

    Kevin

  • tangerinetangerine Posts: 3,342Questions: 35Answers: 394
    edited June 2020

    DateTime is returned by MySQL in seconds only.

    Why? That's not the default behaviour.

    I'm not clear what you're trying to do, but why not have the database return exactly what you want so you don't need to do anything else to the result?

  • etilleyetilley Posts: 31Questions: 4Answers: 0

    @tangerine The default behavior of WtForms with the MySQL extension DATETIME, is to fill Pandas with this unix date time (in seconds).

    All you have to do to get this output in Flask is :

    qry_targetList = """SELECT startdate FROM targets ;"""
    
    cur = conn.cursor()
    df = pd.read_sql(qry_targetList, conn)  // this loads the pandas dataframe with UTC
    cur.close()
    
    return (pd.DataFrame(df).to_json(orient='records'))
    

    After this, you just need to ajax fetch in to datatable.net to get the UTC in seconds. I assume the pandas date format can be changed as well? Or the reforming can happen in JS via a moment ...

  • etilleyetilley Posts: 31Questions: 4Answers: 0
    edited June 2020

    Here are two solutions that seem to work ... from https://stackoverflow.com/questions/33665104/datatables-convert-incoming-epoch-date-to-dd-mm-yyyy

    The second routine on stackoverview doesn't rely on moment.js:

    For one datefield in a Datatable:

    columns: [
                  {"data": "startdate"},
              ],        
    columnDefs: [{
                          targets: 0,
                          render: function(data) {
                          return moment(data).format('YYYY/MM/DD'); }
                          }],
    

    For multiple date fields in a Datatable::

    columns: [
                 {"data": "startdate"},
                 {"data": "enddate"},
                 {"data": "raindate"}
                 ],        
    columnDefs: [{
                 targets: 0,
                 render: function(data) {
                 return moment(data).format('YYYY/MM/DD'); }
                 },
                 {
                  targets: 1,
                  render: function(data) {
                  return moment(data).format('YYYY/MM/DD'); }
                  },
                  {
                  targets: 2,
                  render: function(data) {
                  return moment(data).format('YYYY/MM/DD'); }
                  }],
    
  • kthorngrenkthorngren Posts: 20,141Questions: 26Answers: 4,736
    Answer ✓

    For multiple columns, since the code is the same, you could do this:

    columnDefs: [{
                          targets: [0, 1, 2],
                          render: function(data) {
                          return moment(data).format('YYYY/MM/DD'); }
                          }],
    

    Kevin

This discussion has been closed.