Sorting Dates in Data Tables

  • Resolved Judson Mitchell

    (@judsonmitchell)


    4 years, 7 months ago

    Peter,

    I have a db column in the DATE format (YYYY-MM-DD). When I load up the table on my page, dataTables will transform the date to a textual date but it will not sort properly (i.e, sorts it as a string). I read some support threads on the dataTables forum, but that seems to involve loading moment.js and writing some custom js, which I would like to avoid if possible.

    Do you have any other suggestions for sorting this table by date? Thanks very much.

    The page I need help with: [log in to see the link]

Viewing 6 replies - 1 through 6 (of 6 total)
  • Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    4 years, 7 months ago

    Hi Judson,

    I think the problem is that your date column is sorted naturally:
    https://stackoverflow.com/questions/50836008/jquery-datatable-sort-not-working-for-date-column

    I think this happens when you change the date format. What format are you using? And where did you define it? In general WP settings? Or the plugin settings? Or both?

    Let me try to test a publication with the same settings and then see what needs to be done to fix this. A redefinition of the columnDefs variable might fix this issue.

    Please let me know,
    Peter

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    4 years, 7 months ago

    Hi Judson,

    I was trying to reproduce this issue. It looks like it works fine as long as a column is of type date. When I convert the data type to string I’m getting the same results. Can you please check the data type of your date column? Is the original column converted?

    Thanks,
    Peter

    Thread Starter Judson Mitchell

    (@judsonmitchell)

    4 years, 7 months ago

    Peter,

    Thanks for looking into this. In my MySQL database, the data type for this column is DATE. Data is entered in YYYY-MM-DD format. When I look at Data Explorer for this table, “Columns” has the column data type as “Date”. When I load the table in phpmyadmin, it sorts fine. On my Data Publisher produced page, I notice that JS is transforming the MySQL date format into an English string, e.g, 2019-02-01 becomes “February 1st, 2019”. That’s fine, but when I go to sort the date columns, the sort is done by the first letter of the month.

    Is there somewhere else other than in the table definition that I have to set the data type for the column?

    Thread Starter Judson Mitchell

    (@judsonmitchell)

    4 years, 7 months ago

    Peter,

    Thanks very much for the offer. You are always so helpful! I went into the WP settings and see that is in fact WordPress that is modifying the date format. I changed the date format to MySQL format and the table works fine now. Presumably, I could use moment.js or date-fns to change the display format of the date after sorting, but I think I’ll take a pass on that for now.

    JM

    Plugin Author Passionate Programmer Peter

    (@peterschulznl)

    4 years, 7 months ago

    Good to hear you fixed it Judson! 😊

    Thanks for reporting back,
    Peter

    Moderator Steven Stern (sterndata)

    (@sterndata)

    Volunteer Forum Moderator

    4 years, 6 months ago

    @peterschulznl I’ve deleted your offer to login to your user’s site. I’m am 100% sure you mean well but please never ask for credentials on these forums.

    https://ww.wp.xz.cn/support/guidelines/#the-bad-stuff

    Now for the why: The internet is a wonderful place full of very nice people and a few very bad ones. I’m sure everyone here is very nice however, by giving some ones keys to your house you are trusting they wont steal anything. Likewise the person who takes the keys is now responsible for the house FOREVER.

    If something was to go wrong, then you the author may well legally become liable for damages, which they would not normally have been as their software is provided without warranty.

    Please be aware that repeatedly asking for credentials will result in us escalating this to the plugins team.

    It’s never necessary to do that. Here’s why.

    There are many ways to get information you need and accessing the user’s site is not one of them. That’s going too far.

    You get the idea.

    Volunteer support is not easy. But these forums need to a safe place for all users, experienced or new. Accessing their system that way is a short cut that will get you into real trouble in these forums.

Viewing 6 replies - 1 through 6 (of 6 total)

The topic ‘Sorting Dates in Data Tables’ is closed to new replies.

Tags