Sorting Dates is Not Working For Me
Hi Admin Columns!
I’m trying to sort my users by the last_login info in my database. It is not working the way I would expect, so I came here to see if there was anyone else having this issue. I do see similar issues to this and have come to the suspicion that the way dates are being stored in my database is the culprit.
In my database, today’s date, September 4, 2023, is showing in the last_login field as: 1693848722. The date displays correctly on my WP Users page, but the result of sorting by Last Login seems random.
I’ve never seen this way of storing dates before. Any idea what I can do?
I’m (probably obviously!) not a database expert, so please keep that in mind with any responses. :) Thank you!
OK, did a bit more research. The value in the last-login field seems to be the number of seconds since January 1, 1970. Now to just get it to sort by the number and display it in date format at the same time. That’s where I’m stuck.
I suspect you’re using our Custom Field column. For the pro features Filtering and Sorting the format in which a date is stored matters. By default, we assume that the stored format for dates is in the ISO format YYYY-MM-DD. Your date seems to be stored in the database as a Unix timestamp. You can use some hooks to make everything work for your date. We bundled all hooks in a snippet that you can use on your website.
https://github.com/codepress/admin-columns-hooks/blob/master/snippets/custom-field-unix-dates.php
You can register the hooks with the last line in the snippet. For your use case, you can used this snippet like this:
( new CustomFieldTimestampMapper( 'last_login' ) )->register();
Stefan,
You are correct, I am using a Custom Field column.
Thank you very much! I think I understand your reply. I have questions about one more detail though. Apologies if this is a dumb thing to ask….
I see the snippet you provided, but I don’t know what to do with it. Should I upload the php file into the Admin Columns Pro folder? Do I copy/paste the code into my Child Theme’s functions.php? Is there a different place it should go?
Just bumping this and looking for some direction on where I need to put the snippet you linked me to. Thank you!
Never mind. I found this and it was super helpful:
https://www.wpbeginner.com/beginners-guide/beginners-guide-to-pasting-snippets-from-the-web-into-wordpress/
Your snippet and the line for registering the hooks worked perfectly. Thank you again!!!
Sorry, I missed your latest reply.
But I’m glad you found a way to implement the snippet after all.
Dear support,
we had the same problem with a woocommerce invoice date coming from german market in the order view.
After using your hook, the date can be filtered with a smart filter but not as expected.
If you use the “date between” smart filter the from date get included the “to date” not. That means if there is a datarow with 2023-09-12 and another 2023-09-15 and you set the filter from 2023-09-12 to 2023-09-15, the 09-12 is shown the 09-15 not.
Another issue:
when you set a smart filter e.g. the above – then applying the filter and after that you decide to update the filter argument, let’s say to another “from date” (or any other) and apply the filter again – a 500 error occured – it is something with the URL arguments.
but what we really need, is a filter for that unix timestamp where you can group by month – the same as is implemented for the order date by default in woocommerce order view.
best Markus
Hi Markus,
Thanks for your feedback. You’re right, the between operator for timestamp dates does not work correctly. It takes the beginning of the date selected, so 2023-09-15 will become 2023-09-15 00:00:00. So putting that in a between operation, it will exclude the given end date completely.
We will fix this for the next release. As a temporary workaround, you can safely one day later so in your case 2023-09-16. It will include 2023-09-15 and not 2023-09-16.
We’re also working on big refactor of our search/filtering feature. This will also include a specific year and month operator that lets you easily choose a whole month or year in both filtering and smart filtering. I can’t give a timeframe on that though.
Thx for the fast response Stefan,
good news so far :) happy to see the new and robust filter soon :D
do you know if there is also an issue when changing the smart filter arguments that leads to a HTTP 500?
thx Markus
You’re welcome. Do you mean that you’re having a 500 error when changing the params in the URL related to our smart filtering (ac-rules)? If so, please can you tell me what you’ve changed so I can try to reproduce the issue? Please send it to support@admincolumns.com.
You must be logged in to reply to this topic.