Hi Emanuele,

Thank you for your purchase.

Sorry for the late response, we have some agents that have contracted Covid-19, so our team is cut in half.

There are a lot of examples on web, especially on stackoverflow for such thing.

One of them is this one where you can find a lot of different solution depends how data is saved in your table in database for this field.

https://stackoverflow.com/questions/9251561/convert-timestamp-to-date-in-mysql-query

Best regards.

Hi Emanuele,

1) Can you try to make a  a MySQL view (a stored query), which will return the data that you need, call it e.g. “view1” and then build a wpDataTable based on a simple query like “SELECT * FROM view1″.

2) If that not help, can you pelase export that table from database so we can test it in our local environment. Please note that this is public ticket so in order to we only see that file,please check PRIVATE checkbox before submitting the replay.

Hi Emanuele,

We check your dump file and we can see that this column is not set as datetime type(it is longtext) and do not have proper format for datetime in MySQL. More on this link. This is necessary so datetime filters work  properly in our plugin.

Best regards.

Hey Emanuele

Thanks of the table, it's been forwarded to our developers for review, and as soon as we hear from them, we will let you know.

Sorry for the delay!

Hello again Emanuele.

The data in the Date column (in the database) is incorrect. Our guess is that you only moved the structure to "date", and the strings converted into '0000-00-00', which is not a correct date format for MySQL. You can see more info on this link.

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD hh:mm:ss' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

So, the minimum value date can have in MySQL is 1000-01-01 or NULL. If you replace all 0000-00-00 with "NULL" it will work correctly.

Hi Emanuele

Can you please provide me a temporary WP-admin (administrator) user for your site where this happens, so we could log in and take a look ‘from the inside’ as that’s the most efficient way to see and resolve the issue. 

We do not interfere with any data or anything else except for the plugin (in case that’s a production version of the site), and of course, we do not provide login data to third parties. 

You can write credentials here just check PRIVATE Reply so nobody can see them except us.

Hi again Emanuele

I just mentioned how the structure of DATETIME columns should look like. As mentioned in my previous response, if you take a look at your database table (the one you sent us), you will see cells with '0000-00-00', which is not a valid entry.

It either needs to be 'NULL', or anywhere from '1000-01-01' to '9999-12-31'. Thank you for the remote access, but I can't modify this through your website. You also don't have the "Add new" option under Plugins, so I can't add WP phpMyAdmin plugin to see the database table, and we can only work with the table you sent us before. This is where we noticed the incorrect values, so I'm sure if you check your database table, and modify all entries with '0000-00-00' and change them to 'NULL', that it will work correctly.

If you still have issues after that, please provide me with database access URL and credentials.

Hi again Emanuele.

I can't access phpMyAdmin like this (see the attachment). 

This "16/12/2021" is not the format that's being used in the database. The database format is "2021-12-16", but I can't see your database file anymore (I mistakenly deleted it from my PC).

Can you send it to me again, and can you provide me with direct URL and credentials for the database?

Remember the Private response

Hi again Emanuele

Thank you for the access.

All columns except for "id" and "postid" are saved as "longtext" type:

So, the "data_emoglobina" column (for example) is saved as a string (longtext). It doesn't matter that you changed the type of this column in wpDataTables, it's saved as text and it will never display correct dates until you modify the column type in the database, and add the correct date format (YYYY-MM-DD).

The entry in the database:

Is string, and if you were to change the column type in the database to be "String", it would display the same value. So, in order to keep the "Date" type in wpDataTables, and store dates correctly, you need to change the column type here, and set the date column to be "date", not "longtext". After that you'll be able to store and read the data correctly.