Time zone information lost when using DataTable.render.datetime (docs?)

kc5151

Hi,

this has been answered before in this thread:

https://datatables.net/forums/discussion/74120/any-way-to-specify-format-to-moment-js-but-keep-local-timezone-adjustment

However, I stumbled about the same issue myself and I was really stumped at first.

To re-phrase:

I'm using Ajax in my Datatables and my database (MariaDB) server returns dates in the following format:

• 2024-10-03
• 2024-10-03 00:00:00

without time zone information. Dates are in local time, which is CE(S)T.

It's probably Node.js/mysql2 (can't verify right now since I have the day off ) that then converts this into ISO 8601, so the raw format in my datatable becomes:

• 2024-10-02T22:00:00.000Z (note that it's 2024-10-02 instead of 2024-10-03 because of TZ conversion)

Now, when I use DataTable.render.datetime() without options, everything looks dandy, the correct date (2024-10-03) is being displayed. However, when I supply a custom date format like DataTable.render.datetime("YYYY-MM-DD"), the date being shown is now 2024-10-02 instead of 2024-10-03! So the time zone information somehow gets lost. This is very confusing.

I solved this by registering a custom date renderer and directly using Moment.js instead of DataTable.render.datetime(). When doing it this way, the correct date is being displayed again. So I basically do moment(data, "YYYY-MM-DD") and everything looks fine again.

I don't know if this is a bug or a documentation issue, but maybe something can be done about it .

Cheers.

kc5151

The correct syntax for moment should be:

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

Sorry for that, I can no longer edit the post.

allan

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

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

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

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

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