Format datetime in the editor form

Novidis

We need to convert a datetime field form ISO format to DD/MM/YYYY and back to ISO format when the edit are saved.
We have the following issues:
1) it seems that the format option does not convert at the first render of the edit form but only after the value has been edited
2) when the data are saved it seems there is not an option to convert back to ISO format

For in line edit, we do not have any issue because we use use a prorpietary render() function for type='display'

allan

Ideally the client-side should see only the formatted data. Editor doesn't have an option to change the format of data when editing a data point - it just takes the raw data. So if your db is storing the date as ISO8601, then use your server-side script to convert it to the dd/mm/yyyy format on read. Likewise, on write convert it the other way.

That can be seen in this example.

Allan

colin

Hi @Novidis ,

This page here talks about server-side date formatting,

Cheers,

Colin

Novidis

ok

mihalisp

Allan,in this example there is no Server Side script.It is missing.

I have the same problem.I have datetime field in SqlSqerver and i want to get it to Editor and Datatable in format 25/12/2019 22:30 .
When i use

     ->getFormatter( Format::dateSqlToFormat( 'd-m-Y H:i' ) )

the Datatable field show 'invalid date'.If i don't use getFormatter,it works on the Datatable but in the Editor field it shows the date in format

    2019-04-30 17:49:00.000

which i don't want.I want this 25/12/2019 22:30.

In the Datatable,I render it as follows

{ data: "db.date", "title": "Start_date" ,"render": function(data, type, full) {
  if (type == 'display')
     return moment(new Date(data)).locale('el').format('DD/MM/YYYY HH:mm');
  else
     return moment(new Date(data)).format('YYYY/MM/DD HH:mm:ss');              
 } }

And finally,when i send the date back to db ,what setFormatter should i use?
Is

  ->setFormatter( Format::dateTime( 'd/m/Y H:i', 'Y-m-d H:i:s' ) )

ok?

Thanks.

allan

Sorry about that. Not immediately sure why it is missing, but I've put it up here.

the Datatable field show 'invalid date'.If i don't use getFormatter,it works on the Datatable but in the Editor field it shows the date in format

Have you defined the format property for the datetime input?

Thanks,
Allan

mihalisp

You mean the format in Editor field?Yes,in Editor i use

          {
            label: "Start Date:",
            name: "db.start_date",
            def: function () { return new Date(); },
            type: "datetime",
            format: 'DD/MM/YYYY HH:mm'
          }

Let's see the things from the beginning.

1) We agree that the following render in the Datatable field is necessary for the sorting to be done properly?

   { data: "db.date", "title": "Start_date" ,"render": function(data, type, full) {
     if (type == 'display')
         return moment(new Date(data)).locale('el').format('DD/MM/YYYY HH:mm');
     else
         return moment(new Date(data)).format('YYYY/MM/DD HH:mm:ss');             
    } }

2)The field type in SqlServer is Datetime.
Should i use -->getFormatter (or not),in order to pass it into the Editor and the Datatable?The format i want both in Editor and Datatable fields is '25/04/2019 22:30'.
which getFormatter format should i use ?

When i use

      ->getFormatter( Format::dateSqlToFormat( 'd/m/Y H:i' ) )

the Datatable field it shows 'invalid date'.

When i use

     ->getFormatter( Format::dateTime( 'Y-m-d H:i:s', 'd/m/Y H:i' ) )

it returns 'null' date in JSON and in Datatable it shows only '01/01/1970 02:00'.

If i don't use getFormatter,it works on the Datatable (but it does not sort the dates in a right way),but in the Editor field it shows the date in format '2019-04-30 17:49:00.000' (it comes straight from the db?) which i don't want.I want this 25/12/2019 22:30.

3)Which one of these i should include.I use el(Greek) locale ?I have included all of them,maybe there is a conflict?

     <script src="bower_components/moment/min/locales.min.js"></script>--> 
     <script src="bower_components/moment/min/moment.min.js"></script>
     <script src="bower_components/moment/min/moment-with-locales.min.js"     charset="UTF-8"></script>
     <script src="bower_components/moment/min/datetime-moment.js"></script>

mihalisp

After a lot of research,i found out that the getFormatter with "/" does not work at all.
Instead,the only solution that works is 'Y-m-d H:i' or no getFormatter at all.

'Y-m-d H:i' shows '2019-04-30 09:48' both in Datatable and Editor.
Without 'getFormatter' shows '2019-04-30 09:48:00.000' both in Datatable and Editor.

Why can't i convert Sql datetime to the desired format 'm/d/Y H:i' ?

In Editor,when i create New record,the format: 'DD/MM/YYYY HH:mm' that i use as field property, works.
When,i Edit or Duplicate a record , the Editor 'Start_date' field shows the retrieved record date as '2019-04-30 09:48' or '2019-04-30 09:48:00.000' accordingly.It does not follows the format: 'DD/MM/YYYY HH:mm'.

I am so confused...

rf1234

@mihalisp
Why don't you try a custom getFormatter that can do anything you like?

Here is an example for a get and a set Formatter for dates. Might not be the best example but it highlights what you can do. I support two languages English (UK) and German (Germany) with their respective date formats. For crappy or empty dates I return NULL from the set formatter and SPACE from the get formatter.

The setFormatter:

function setFormatterDate($val = null) {  
    //Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: 
    //if the separator is a slash (/), then the American m/d/y is assumed; 
    //whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed. 
    //If, however, the year is given in a two digit format and the separator is a dash (-, the date string is parsed as y-m-d.
    if ( is_null($val) ) {
        return $val;
    }
    if ( $val <= '' ) {
        return null;
    }
    $val = str_replace(
        ['Januar ', 'Februar ', 'März ', 'April ', 'Mai ', 'Juni ', 'Juli ',
            'August ', 'September ', 'Oktober ', 'November ', 'Dezember '],
        ['January ', 'February ', 'March ', 'April ', 'May ', 'June ', 'July ',
            'August ', 'September ', 'October ', 'November ', 'December '],
        $val);
    $val = str_replace('/', '-', $val);
    $val = str_replace('.', '-', $val);
    
    if ($val == '00-00-0000' || $val == '0000-00-00 00:00:00') {
        return null;
    }
    
    $dateTime = new DateTime($val);    
    return $dateTime->format('Y-m-d H:i:s');
}

The getFormatter:

function getFormatterDate(&$val, $format = null, $lang = null) {
    if ($val == null) {
        return '';
    } else {
        $date = new DateTime($val);
    }
    //if language doesn't get passed and we are in batch: we use German format
    if ( is_null($lang) ) {
        if ( isset($_SESSION['lang']) ) {
            $lang = $_SESSION['lang'];
        } else {
            $lang = 'de';
        }
    }
    if ( is_null($format) ) {
        if ($lang === 'de') {  
            return $date->format("d.m.Y");
        } else {
            return $date->format("d/m/Y");
        }
    } else { //other formats Ymd and dmy
        if ( $format === 'Ymd' ) {
            return $date->format("Ymd");
        } elseif ( $format === 'dmy') {
            if ($lang === 'de') {  
                return $date->format("d.m.y");
            } else {
                return $date->format("d/m/y");
            }
        } elseif ( $format === 'dm') {
            if ($lang === 'de') {  
                return $date->format("d.m.");
            } else {
                return $date->format("d/m/");
            }
        }
    }
    return $val;
}

How to call them:

Field::inst( 'contract.start_date' )
        ->getFormatter( function ( $val, $data, $opts ) {
            return getFormatterDate($val);                 
        } )
        ->setFormatter( function ( $val, $data, $opts ) {
            return setFormatterDate($val);
        } ),

allan

Should i use -->getFormatter (or not),in order to pass it into the Editor and the Datatable?The format i want both in Editor and Datatable fields is '25/04/2019 22:30'.

For type === 'display' || type === 'filter' just return data; its already in the format you want to display. No need to use Moment to attempt to transform it again.

After a lot of research,i found out that the getFormatter with "/" does not work at all.

That shouldn't be the case, as can be seen here.

Allan

mihalisp

Thank you for your responses,but i still can't figure out why it doesn't work on my case.

Allan,in the examples https://editor.datatables.net/examples/dates/datetime.html

and https://editor.datatables.net/examples/dates/formatting.html

the sorting in the date columns does not work in a right way.Neither in my datatable if i follow your advice and not use moment to transform it again.
So,if i use

     ->getFormatter( Format::dateSqlToFormat( 'd/m/Y H:i' ) )

and

     { data: "db.startdate", "title": "startDate" ,"render": function(data, type, full) {
        if ((type == 'display') || (type === 'filter'))
          return data;
          else return data; //or return moment(new Date(data)).format('YYYY/MM/DD HH:mm:ss'); 

}

then the sorting does not work!!!
That's why i use moment for display which works fine.
I want the datatable to be sorted on the startdate column by default.

I am still confused,i have been working on it for days and still can't make it work properly.

Can you please give me the correct guidelines about how to handle it both in server side script and on the Datatable-Editor.

mihalisp

Well,i finally found a solution after a lot of hours of trial and error.

The ulimate plugin for datetime columns' sorting did the trick.

     $.fn.dataTable.moment( 'DD/MM/YYYY HH:mm' );

And this way the ->getFormatter( Format::dateSqlToFormat( 'd/m/Y H:i' ) ) works.

1) a)How many times(for how many datetime dolumns) may i use the ultimate plugin?I mean,what if i want 2 or 3 columns with different datetime format than the first one?

b)How can i use the $.fn.dataTable.moment( 'DD/MM/YYYY HH:mm' ); in this situation?

c)Can i use iti for only one column (like column render) or it treats all the datetime columns the same way?

2)I don't really understand why the render in the column does not work?it returns invalid date( or null).

     ..."render": function(data, type, full) {
           if (type == 'display')
              return moment(new Date(data)).locale('el').format('DD/MM/YYYY HH:mm');
            else
                return moment(new Date(data)).format('YYYY/MM/DD HH:mm:ss'); 

3)The following throws error.Is it deprecated?

                'columnDefs': [ {
                    'targets': 7,
                    'render': $.fn.dataTable.render.moment( 'DD/MM/YYYY HH:mm' )
                  } ],

Finally,with great respect,i would like to point out that the examples for Datatables and Editor are really helpful,but they are not complete and don't cover many aspects together.They cover seperate functions but when you put them together you realize that it is not so easy as it seems in examples and this takes you a lot of time to deal with it.

For example,the datetime formatting examples are great,but there is no mention in them about proper datetime column sorting(ultimate plugin).

It would be extremely helpful for many peaople here if you created some more complex examples to include many functions together.

Thank you in advance!

allan

Thanks for your feedback here - this is clearly something we need to do some work on! I'm going to have the Moment sorting built into v2 of DataTables which will help somewhat.

Its particularly bad that the examples don't sort correctly - I've filed a bug for that and it should be fixed for 1.9.1.

i use the ultimate plugin?I mean,what if i want 2 or 3 columns with different datetime format than the first one? [and b. c.]

Use it with as many formats as you want. It will automatically detect them once it has been told want format(s) to look for (there are any number of possible combinations which is why it doesn't attempt to just do it out of the box).

2)I don't really understand why the render in the column does not work?it returns invalid date( or null).

Because moment, there, hasn't been configured to read in the formatted date that PHP is giving it. It could be parsed, but it isn't really needed.

3)The following throws error.Is it deprecated?

What error is thrown? That looks okay to me (assuming the data for that column is ISO8601).

Allan

mihalisp

3)the error is $.fn.dataTable.render.moment is not a function.
I have included the datetime-moment.js .

mihalisp

$.fn.dataTable.moment works
but $.fn.dataTable.render.moment is not a function.

Why?
What's the difference?

mihalisp

I see the difference but why it is not a function.I include moment with locales etc.

kthorngren

but $.fn.dataTable.render.moment is not a function.

I think you need to install this plugin for that render function:
https://datatables.net/plug-ins/dataRender/datetime

Kevin

allan

I'm with you now - yes as Kevin alludes, the render.moment one is a different plug-in.

• $.fn.dataTable.render.moment is for using Moment to format the output of a cell (you don't need this since you've got the server formatters doing it already).
• $.fn.dataTable.moment is to deformat a human readable date string (shown in the table) into something DataTables can date sort. This you do need (unless you get fancy with orthogonal data - but we'll save that for another day).

Allan

EricD

I made this work with solution below
Hope this help you

[ pets.html ]

            <script type="text/javascript" src="https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.8.4/moment.min.js"></script>
    <script type="text/javascript" src="https://cdn.datatables.net/plug-ins/1.10.19/sorting/datetime-moment.js"></script>

        var editor;
        $(document).ready(function () {
            $.fn.dataTable.moment('D/M/YYYY');
            editor = new $.fn.dataTable.Editor({
                ajax: "controllers/pets.php",
                table: "#pets",
                fields: [{
                        label: "DOB:",
                        name: "db_pets.dob",
                        type: "datetime",
                        format: "DD/MM/YYYY"
                    }
                ],
            });
            $('#pets').DataTable({
                dom: "Bfrtip",
                ajax: "controllers/pets.php",
                columns: [
                    {data: "db_pets.dob"},
                ],
                columnDefs: [
                    { target: 0, type: "datetime-moment" }
                ],

            });
        });

[ cpets.php ]

Editor::inst($db, 'db_pets')
    ->fields(
        Field::inst('db_pets.dob')
            ->validator(Validate::dateFormat('d/m/Y'))
            ->getFormatter(Format::dateSqlToFormat('d/m/Y'))
            ->setFormatter(Format::datetime('d/m/Y','Y-m-d')),
    ->process($_POST)
    ->json();

rf1234

Everybody is getting really into this I see!

@EricD I absolutely agree. I would always do get formatting on the server. I think it is much easier, particularly if you would like to use your own get formatters.

The only remaining issue really is sorting the formatted values client side when it comes to dates, international chars and formatted numbers. I wrote this a while ago summarizing what you can do about it. I had to implement all of this myself, too.

https://stackoverflow.com/questions/53238318/html5-javascript-datatables-order-sort-of-columns/53238593#53238593

Hope this helps!

mihalisp

Thank you guys.

The matter is weather you format the dates on the server or not before you send them to client (where you also want them to be properly sorted).
In another ,almost the same ,implementation i had earlier with dates,i didn't make the date format on the server but on client and there was the problem because i was confused.I had to use both datetime transformation (.render.moment) and datetime-moment (.moment sorting).
Instead of .render.moment i used the following in column.render

  if (type == 'display')
   return moment(new Date(data)).locale('el').format('DD/MM/YYYY HH:mm');
  else
  return moment(new Date(data)).format('YYYY/MM/DD HH:mm:ss');             

What about the null or invalid dates?Are they handled well or they need extra code to use for proper display and sorting?

The help from the community in forums is really good,but I think the admins should write some articles with examples explaining the differences between functions that do almost the same e.g. 1)datetime server/client formatting with correct sorting/filtering,
2)benefits/differences of datatable filtering in client or server through dropdown lists,comboboxes,checkboxes.
And many more real life examples.

It would be extremely helpful to all the community.

Thanks again!

allan

What about the null or invalid dates?

An empty string is handled, but it is expected that there will be valid date otherwise (actually, otherwise the auto detection for that column's sorting wouldn't work).

An update to the date time examples which includes full sorting support (annoyed with myself for having missed that before!) should help clear things up.

Allan

mihalisp

Continuing the thread,i still have in my Datatables a lot "Invalid date" cells.

I want the Day Column to display the date from StartDate column in 'ddd' format.
I can't find what is going wrong.
The getformatter from the serverside script is:->getFormatter( Format::dateSqlToFormat( 'd/m/Y H:i' ) )

There are no null dates in column StartDate.

This is what i use:

  { data: null , "title": "Day", "render": function(data, type, row) {
      return moment(new Date(row.StartDate)).locale('el').format('ddd');

  /* I have also tried the following but no success.
  if (moment(new Date(row.StartDate)) != null) {
       if(moment(new Date(row.StartDate)).format("ddd") == 'Invalid date')
           { return moment(new Date(row.StartDate)).format('ddd') ;  }
        else
           { return moment(new Date(row.StartDate)).locale('el').format('ddd'); }
     }    
   else return moment(new Date(row.StartDate)).locale('el').format('ddd'); */            
 }  }, 

 { data: "StartDate", "title": "Start Date" }

Date handling is so frustrating...

rf1234

you might want to take a look at this post for checking on a valid date with moment.js
https://stackoverflow.com/questions/19978953/moment-js-isvalid-function-not-working-properly
I use "isValid()" as well and it works fine for me.

It is also important to take a look at your moment locale file to figure out what moment.js really uses:
https://cdnjs.cloudflare.com/ajax/libs/moment.js/2.24.0/locale/el.js

To avoid trouble I would recommend to implement the following approach:
- do validation, get and set formatting on the server only
- hence you only deal with formatted dates on the client side; for empty or invalid dates the make the server return SPACE for example
- you would only need moment.js for client side date calculations and language specific rendering (e.g. for Greek local file "el" and for English(UK) locale file "en-gb")

mihalisp

Thank you.
The solution may be simpler.
The dates that return as 'Invalid date' are those that have day >12.

Since i use ->getFormatter( Format::dateSqlToFormat( 'd/m/Y H:i' ) ) ,what is the proper way to transform it with column rendering as ddd ?

I don't have null dates returned ,only valid values d/m/Y H:i .
Why return moment(new Date(row.empio.empio_dtin)).locale('el').format('ddd');
doesn't transform it correctly?

What should i change for the render to transform it correctly?

mihalisp

The JSON return from the serverside script is

   {"emp_id":"1551","startdate":"11\/12\/2013 09:00","......

Why are there \ / \ / backslashes???

rf1234

good question ... I have no clue ...
I use custom get and set formatters (see above). And I use PHP to transform the dates, not the Editor built in functions

a simple custom get formatter that will return 31/12/2018 09:00 or SPACE could look like this

Field::inst( 'yourDateReadFromDatabase' )
    ->getFormatter( function ( $val, $data ) {
            if ( $val == null ) {
                return '';
             }
             $date = new DateTime($val);
             return $date->format("d/m/Y H:i");
    } )

and the most basic setFormatter would simply do this:

->setFormatter( function ( $val, $data, $opts ) {
    if ($val <= '' || $val == null) {
       return null;
    }
    $val = str_replace('/', '-', $val);
    $dateTime = new DateTime($val);    
    return $dateTime->format('Y-m-d H:i:s');
} ),

You should replace the "/" with "-" before transforming to make sure PHP does not assume the US date format (Month, Day, Year) as input date but the European format (Day, Month, Year).

These are the PHP date disambiguation rules I copied from the manual:
"Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components:
if the separator is a slash (/), then the American m/d/y is assumed;
whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed.
If, however, the year is given in a two digit format and the separator is a dash (-, the date string is parsed as y-m-d."

rf1234

And my final one for today ...

If you want "Monday, 11/12/2018 09:30" with PHP do this:

Field::inst( 'yourDateReadFromDatabase' )
   ->getFormatter( function ( $val, $data ) {
        if ( $val == null ) {
            return '';
         }
         $date = new DateTime($val);
         $dayLong = jddayofweek(cal_to_jd(CAL_GREGORIAN, 
                    $dateTime->format("d"), $dateTime->format("m"), 
                    $dateTime->format("Y")), 1 );
         return $dayLong . ', ' . $dateTime->format("d/m/Y H:i");
    } )

allan

Why are there \ / \ / backslashes???

That's just the technically correct way to use a / in JSON. The PHP JSON encoder is adding it.

Since i use ->getFormatter( Format::dateSqlToFormat( 'd/m/Y H:i' ) ) ,what is the proper way to transform it with column rendering as ddd ?

I don't have null dates returned ,only valid values d/m/Y H:i .
Why return moment(new Date(row.empio.empio_dtin)).locale('el').format('ddd');
doesn't transform it correctly?

Because new Date() can't read anything other than an ISO8601 string reliably - see MDN (Actually Chrome will attempt to parse just about anything and is very good at it, but ISO8601 is the only one that is reliable).

Instead you would use something like:

return moment( row.empio.empio_dtin, 'DD/MM/YYYY HH:mm' ).locale('el').format('ddd');

Note how I've used the second parameter for moment to tell it what format the empio_dtin variable is in.

Allan

mihalisp

Wow,it worked , Allan!

These are some details that are not easy to find.
EU dates manipulation must have been difficult to deal with for many people working with Datatables.

Thank you both!!!