How to sort records Chronologically?

[urichard]

Hi,

I noticed that the sorting of my records created only sorts it according to value and not date as shown in my screenshot below, how to sort it so it will accurately show the oldest records at the bottom and newest on top when i click the column header "created"?

[jsetzer]

Sorting looks correct in the screenshot.

You have decided to put a formatted datetime + a string containing user-info into one field. This results in a alphanumeric string.

So, the displayed values are alphanuneric strings, not dates. Sorting is alphanumeric.

You may consider defining a custom sorting in init-hook.

See DefaultSortField here:
https://bigprof.com/appgini/help/advanc ... ist-object

You would need SQL for getting the first 20 to 22 characters of your string and convert them from your specific display format into a valid MySql date format for sorting.

For example convert 7/12/2022 into 2022-12-07.

Caution: the display format you have chosen creates different lengths, because you are not strictly using 2-digits for day (and perhaps for month).

I strongly recommend always using dd/mm/yyyy. Especially if you plan to work with date values later on and not just display them.

But things could be much easier

I recommend using two separate fields for date and user. This would allow you to sort by date and also sort by user out of the box without need for any string parsing nor conversions.

[urichard]

Okay Thanks Jsetzer,

I will give it a try and update here once I'm done

[grimblefritz]

Unless you expect your app to still be viable in 2038 (the 2038 problem), I would use TIMESTAMP for metadata like fields for created or modified dates. Meta timestamps are not normally edited (by a human) so the DATE or DATETIME types aren't typically needed in this context.

TIMESTAMP is stored as a number (and in UTC time, not local timezone) and will sort like a number. Great for system-generated metadata, not so much for user input.

The caveat, as I mentioned, is that unless the 2038 problem is resolved with a universal standard, then it is possible your app only has about 15 years of life left in it. I view this like the infamous Y2K bug. It was a real issue, but it was not the end of the world as many predicted. Humans solve problems, and I am confident the 2038 problem will be a nothingburger by the time 2038 arrives.