Answers

• kc5151 Posts: 46Questions: 4Answers: 0

  October 3

  The correct syntax for moment should be:

  moment(data)("YYYY-MM-DD")

  Sorry for that, I can no longer edit the post.
• allan Posts: 63,158Questions: 1Answers: 10,405 Site admin

  October 3

  Thanks for posting this - I can see it here as well: https://live.datatables.net/kixurovu/1/edit .

  Its an interesting one for sure, as 2024-10-02 is actually correct, if not desired. That is the local date part of what it has been given. I wonder if there needs to be a flag somewhere to convert it to UTC before display.

  If you are able to have the server return it in UTC, then that would be the better option. What server-side code are you using for this?

  Allan
• kc5151 Posts: 46Questions: 4Answers: 0

  October 3

  I'm using a simple SELECT date_field FROM table to retrieve the data from MariaDB, so there is no TZ information attached at this point. It ends up as a Date object (with time zone information) in my Datatable, so I guess it must be Node.js/mysql2 that converts this.

  I've modified your example a bit, to also display the different render methods.

  https://live.datatables.net/nuripufo/1/edit

  The results are quite strange . Eg. when there is no time zone information attached, DataTable.render.datetime() without arguments is wrong, at least in my time zone. The only thing that is consistently correct, is the direct call of the moment() renderer.

  What I'm seening in the table (local browser is set to CEST) is the following:

  2.9.2024, 00:00:00 - 2024-09-01 22:00:00 - 2024-09-02 00:00:00
  1.10.2024, 02:00:00 - 2024-10-01 00:00:00 - 2024-10-01 00:00:00

  What I would have expected is 2024-09-02 00:00:00 and 2024-10-01 00:00:00 consistently (like moment() does it).

  In my use case, it doesn't make sense to show different dates to users living in different time zones (well, everybody who is going to work with the data lives in the same TZ fortunately). So maybe tomorrow I can find out where the TZ info is actually coming from in my use case and disable it altogether.

  Google says, I could try enabling the dateStrings option in mysql2 to disable the conversion into JavaScript date objects: https://github.com/mysqljs/mysql#connection-options. I'll check if it makes things better or worse
• rf1234 Posts: 2,937Questions: 87Answers: 415

  10:35AM edited 10:38AM

  Sounds like a lot of trouble ...

  Have you considered to do the date and time rendering on the server using Editor with a getFormatter and a setFormatter? That is pretty easy. I guess the timezone is kind of taken from the server but I found this in my code serverside. That seems to be doing it for me.

  date_default_timezone_set('Europe/Berlin');
  

  This would work if your local date format is DD.MM.YYYY.

  Field::inst( 'your.date_field' )
      ->getFormatter( function($val, $data, $opts) {
          if ( $val == null ) {
              return '';
          }
          $dateTime = new DateTime($val);
          return $dateTime->format("d.m.Y");
      })
      ->setFormatter( function($val, $data, $opts) {
          if ( $val == null || $val <= '' || 
               $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');
      }),  
  
• kc5151 Posts: 46Questions: 4Answers: 0

  2:20PM

  Hi rf1234. Thanks for the suggestion, however, I currently do not have an Editor license. Also, the problem doesn't appear when directly using moment(), so I assume something is amiss with DataTable.render.datetime().
• allan Posts: 63,158Questions: 1Answers: 10,405 Site admin

  2:26PM

  Definitely work giving dateStrings a go. I agree, from your example, that there is something not right going on - I need to look at that closer. Thanks for the example!

  Allan