[Bug-2737] Perspective table - sorting date column

Running Ignition 8.0.6, we have a table with a column full of timestamps. We set the columns.render property to “date” and sortable to true. When we sort the date column, it seems to be sorting based on the string-formatted time rather than the actual timestamp.

For example, if I had the dateFormat set to “MM/DD/YYYY” and sorted by date, I might see these items in this order:

12/16/2019
12/06/2019
01/05/2020

Note that the “12/06/2019” entry is out of order, I suspect because ‘0’ comes after ‘2’ in string ASCII sorting. Even worse, I suspect that if I had another entry from January of 2019, it would show up adjacent to the Januar 2020 entry instead of at the beginning.

I expected the items to be sorted in chronological order.

This happens in 8.0.7 too.

It will go 3/29 -> 3/3 -> 3/30 -> 3/31

I sent in an email to Inductive Support and if you render the column as ‘auto’ instead of date the sorting works correctly. I don’t think it’s being dealt with as a bug just as designed.

The formatting still works.

2 Likes

Did you ever find a solution to this? It still seems to not work with either option in 8.1.1

Nope I just turned off sorting on columns where it didn’t work.

This is why YYYY/MM/DD is the superior format. Sort with regular old sort. Users will learn to accept it

If only dealing with users was that easy :rofl:

“Do you want sort or do you want stupid format? You can’t have both. You know why the rest of the world uses YYYY/MM/DD? This is why. What’s the deal with imperial measurement anyway–who the hell thought base 12 was a good idea? And don’t get me started on our healthcare-” “Okay, sure, I can do it wrong if that’s what you want but like I said they won’t sort.” “Oh you don’t care that much just get it done? 10-4”

In the long run, we used something similar to this as a transform on our dataset binding, so the table is pre-sorted by dates:

def sort_dataset_by_date(dset, date_column, ascending=True):
	orig_columns = system.dataset.getColumnHeaders(dset)
	tstamps = [system.date.toMillis(x) for x in dset.getColumnAsList(date_column)]
	dset = system.dataset.addColumn(dset, 0, tstamps, 'tmp_tstamp', long)
	dset = system.dataset.sort(dset, 0, ascending)
	return system.dataset.filterColumns(dset, orig_columns)

If you’re using this for a table component, you can leave the tmp_stamp column in place and just not display it.

2 Likes

I tested this on 8.1.5, and sorting works for me if props.columns[N].render is set to auto
image

Unfortunately it’s still busted if you set render to date
image

Ignition version 8.1.6 contains several bug fixes which had impacted the sorting of Table columns, including the ability to sort a column by date. In 8.1.6 and newer versions, you should find that timestamps and string dates sort appropriately. Please reply in this thread with any feedback or concerns while using 8.1.6 or newer.

Definitely still broken for date sorting in 8.1.6.

Original table data is date…

image

Sorting is still all over the place with render: auto OR render: date

Maybe part of the problem is at least in my case there are null values in some of the rows. However, I would still expect it to sort the non null values correctly.

Is there anyway you could provide a mock up reproducing the issue so I can give it a look. I tried reproducing it with a dataset tag as well as trying to mimic your data structure without success. Thanks.

I was finally able to reproduce this issue and have since opened an internal ticket.

2 Likes

Ok, cool. I just reproduced it in a view as well. (See attached). I made a dummy DB (MSSQL) query to generate some random dates and am able to replicate the issue consistently.

scratch_2021-06-07_1232.zip (22.6 KB)

I’m unable to understand the date sorting in 8.1.7
I’ve tried

  • Remove order by in my named query

  • Change render on my table’s column to auto or date
    and it’s same false result

The date format is “DD/MM/YYYY HH:mm” because of French users

strangely, on some views with the same column configuration sorting on dates values seems to work.
I have an example with rows from the audit_logs SQL table that sorts correctly into the perspective table.

This is currently in development, we will be sure to check this scenario during testing.
Thanks!